Friday, February 3, 2012

Open Interfaces for Data Migration


Oracle General Ledger
• Budget Upload
• Importing Journals
• Loading Daily Rates 
Oracle Payables
    • Credit Card Transaction Interface Table
    • Invoice Import Interface. This interface is no longer supported for importing invoices.
……..Use the Payables Open Interface instead.
    • Payables Open Interface
    • Purchase Order Matching 
Oracle Receivables

    • AutoInvoice
    • AutoLockbox
    • Customer Interface
    • Sales Tax Rate Interface
    • Tax Vendor Extension 
Oracle Assets

    • ACE Interface
    • Budget Open Interface
    • Mass Additions Interface
    • Production Interface
    • Physical Inventory
Oracle Cash Management

    • Bank Statement Open Interface
    • Forecasting Open Interface
    • Reconciliation Open Interface
Oracle Purchasing

    • Requisitions Open Interface
    • Purchasing Documents Open Interface
    • Receiving Open Interface
Oracle Inventory

    • Customer Item Interface
    • Open Item Interface
    • Open Replenishment Interface
    • Open Transaction Interface
    • Cycle Count Open Interface
    • Reservations Open Interface
    • Move Orders Open Interface
Oracle Projects

    • Activity Mangement Gateway
    • Client Extensions
    • Transaction Import
What is Inventory
In simple terms it is a stock of items that your business is selling in order to make a living. Almost every non-IT related business has some physical items to sell and hence Inventory is at the heart of almost any business and is central to the planning process.
The considerations in setting it up are at least as complex as those for any other single application. Commonly, Inventory will be installed during the implementation of Order Entry, Purchasing, Manufacturing, or Supply Chain Management but it is an extremely complex module in its own right with several important setup considerations.
Inventory System: 
The simplest function of an Inventory system is to keep track of your items i.e. which item came in and which item went out of inventory and when the item count reaches below a certain threshold value then alert the responsible person to replenish it soon. So how does items go in and out of the warehouse? Let say your company manufactures office chairs. When a customer orders 5 chairs and when you ship them to the customer the count of the item goes down and when you manufacture them internally and when the finished chair is ready and moved to the warehouse the item count increases. An item can be internally consumed too.
An inventory system provides physical management of a company’s stock until it is either used up or sold. It also performs a logical, cataloging function. This stock is referred to by its item number  (usually item number is the segment1 column of mtl_system_items) and has many attributes that affect the transactions that the modules can process against the item.
Every other module within Oracle Applications that needs to name types of objects looks to the Inventory Item Master for details regarding an item. These objects include products ordered through Oracle Order Entry, items invoiced through Oracle Receivables, items purchased through Oracle Purchasing, items paid for through Oracle Payables, items maintained through Oracle Service, and items charged against projects in Oracle Projects.
Every module in the Manufacturing suite (Engineering, Bills of Material, Work in Process,  Master Production Schedule/Material Requirements Planning, and Cost Management) deals with items named in Inventory.
Inventory’s can also track intangibles like magazine subscriptions. The company can sell these items along with physical objects. Assemblies that are never stocked can also be defined in Inventory. A phantom item, for example, represents an intermediate step in manufacture, an assembly that is never stocked in inventory because it is used immediately in a higher-level assembly.
Oracle Inventory’s on-hand balance is increased when an item is bought or made. Inventory relinquishes control and decreases its balance when items are sold or put to their final use within the company. Pads of paper in the stock room are inventory; the same pad of paper on somebody’s desk is not-as far as the company is concerned, it is already used. A new desk in the warehouse belongs to inventory; a desk in somebody’s office is no longer in inventory. Many major purchases, such as plant and equipment, never have an on-hand balance because they are put to their final use
as soon as they are bought.
Inventory Tables:
MTL_CATEGORIES_B 
MTL_CATEGORIES_B is the code combinations table for item categories. Items are grouped into categories with in the context of a category set to provide flexible grouping schemes. The item category is a key flex field with a flex code of MCAT. 
The flexfield structure identifier is also stored in this table.MTL_CATEGORY_SETS_B
MTL_CATEGORY_SETS_B contains the entity definition for category sets. A category set is a categorization scheme for a group of items.Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes.
 
An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Only categories with the same flexfield structure may be grouped into a category set. 
CONTROL_LEVEL defines whether the category set is controlled at the item or the item/organization level. When an item is assigned to an item level category set within the item master organization, the category set assignment is propagated to all other organizations to which the item is assigned.
VALIDATE_FLAG defines whether a list of valid categories is used to validate category usage within the set. Validated category sets will not allow item assignment to the category set in categories that are not in a predefined list of valid categories. Category Sets now support multilingual category set name and description.
MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.
 
MTL_ITEM_CATEGORIES: 
MTL_ITEM_CATEGORIES stores the item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items may be assigned to multiple categories and category sets but may be assigned to only one category in a given category set. This table may be populated through the Master Items and Organization Items windows. It can also be populated by performing item assignments when a category set is defined. It is also populated when an item is transferred from engineering to manufacturing.

MTL_ITEM_SUB_INVENTORIES 

MTL_ITEM_SUB_INVENTORIES maintains a listing of subinventories assigned to an inventory or engineering item. These sub inventories make up the list of valid subinventories when transacting this specific item and the user has specified (in the master window) that the item must use subinventories restricted to a pre–defined list.
Oracle Inventory Interfaces
• Customer Item Interface
• Open Item Interface

 Open Replenishment Interface
• Open Transaction Interface
 Cycle Count Open Interface
• Reservations Open Interface 
• Move Orders Open Interface

§  Item import (Item conversion)

    The Item Interface lets you import items into Oracle Inventory. 

 

Pre-requisites:

 

  1. Creating an Organization
  2. Code Combinations
  3. Templates
  4. Defining Item Status Codes
  5. Defining Item Types
     
 
 Interface tables:          
 
  1.                      MTL_SYSTEM_ITEMS_INTERFACE
  2.                      MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
  3.                      MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
  4.                      MTL_INTERFACE_ERRORS (View errors after import)
    
Concurrent Program : =>                      Item import
 
In the item import parameters form, for the parameter ‘set process id’, specify the ‘set process id’ value given in the mtl_item_categories_interface table. The parameter ‘Create or Update’ can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.
 
 
Base Tables:             
  1. MTL_SYSTEM_ITEMS_B              
  2. MTL_ITEM_REVISIONS_B
  3. MTL_CATEGORIES_B
  4. MTL_CATEGORY_SETS_B
  5. MTL_ITEM_STATUS          
  6. MTL_ITEM_TEMPLATES
 
 
 
 

Validations:    
      
  1.            Check for valid part_id/segment of the source table.
  2.            Validate part_id/segment1 for master org.
  3.            Validate and translate template id of the source table.
  4.            Check for valid item type.
  5.            Check for valid template id. (Attributes are already set for items, default attributes for that template      i.e., purchasable, stockable, etc )
  6.            Check for valid item status.
  7.            Validate primary uom of the source table.
  8.            Validate attribute values.
  9.            Validate other UOMs of the source table.
  10.            Check for unique item type. Discard the item, if part has non-unique item type.
  11.            Check for description, inv_um uniqueness
  12.            Validate organization id.
  13.            Load master records and category records only if all
  14.            Load child record if no error found.
 
 
 
 
Some important columns that need to populated in the interface tables:
 
MTL_SYSTEM_ITEMS_INTERFACE:
 
PROCESS_FLAG = 1
 
(1= Pending,
2= Assign Complete,
3= Assign/Validation Failed,
4= Validation succeeded; Import failed,
5 = Import in Process,
7 = Import succeeded)
 
 
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from  MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)
 
 
 
MTL_ITEM_CATEGORIES_INTERFACE:
 
INVENTORY_ITEM_ID or ITEM_NUMBER.
 
ORGANIZATION_ID or ORGANIZATION_CODE or both.
 
TRANSACTION_TYPE = ‘CREATE’ (‘UPDATE’ or ‘DELETE’ is not possible through Item Import).
 
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
 
CATEGORY_ID or CATEGORY_NAME or both.
 
PROCESS_FLAG = 1
 
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if
you are importing item and category assignment together)
 
 
 
MTL_ITEM_REVISIONS_INTERFACE:
 
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the
 
ORGANIZATION_ID or ORGANIZATION_CODE or both
 
REVISION
 
CHANGE_NOTICE
 
ECN_INITIATION_DATE
 
IMPLEMENTATION_DATE
 
IMPLEMENTED_SERIAL_NUMBER
 
EFFECTIVITY_DATE
 
ATTRIBUTE_CATEGORY
 
ATTRIBUTEn
 
REVISED_ITEM_SEQUENCE_ID
 
DESCRIPTION
 
PROCESS_FLAG = 1
 
TRANSACTION_TYPE = ‘CREATE’
 
SET_PROCESS_ID = 1
 
Each row in the mtl_item_revisions_interface table must have the REVISION and
 
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

§  Inventory On-hand quantity Interface



This interface lets you import the on hand inventory into Oracle.
    
 
Interface tables:
 
MTL_TRANSACTIONS_INTERFACE
 
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
 
MTL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
 
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG.
 
Only records with TRANSACTION_MODE of 3, LOCK_FLAG of ’2′, and PROCESS_FLAG of ’1′ will be picked up by the Transaction Manager and assigned to a Transaction Worker.
 
If a record fails to process completely, then PROCESS_FLAG will be set to ’3′ and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
 
Base Tables:
 
MTL_ON_HAND_QUANTITIES
 
MTL_LOT_NUMBERS
 
MTL_SERIAL_NUMBERS
 
Validations:
 
Validate organization_id
 
Check if item is assigned to organization
 
Validate disposition_id
 
Check if the item for the org is lot controlled before inserting into the Lots interface table.
 
Check if the item for the org is serial controlled before inserting into Serial interface table.
 
Check if inventory already exists for that item in that org and for a lot.
 
Validate organization_id, organization_code.
 
Validate inventory item id.
 
Transaction period must be open.
 
Some important columns that need to be populated in the interface tables:
 
MTL_TRANSACTIONS_INTERFACE:
 
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
 
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
 
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL –
 
If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
 
TRANSACTION_TYPE_ID,
 
PROCESS_FLAG
(
         1 = Yet to be processed,
         2 = Processed,
         3= Error
)
 
TRANSACTION_MODE
 (
2 = Concurrent – to launch a dedicated transaction worker to explicitly process a set of transactions,
3 = Background – will be picked up by transaction manager polling process and assigned to
        transaction worker. These will not be picked up until the transaction manager is running
)
 
SOURCE_CODE,
 
SOURCE_HEADER_ID,
 
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
 
TRANSACTION_SOURCE_ID
 
Source Type
Foreign Key Reference
Account
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias
MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule
WIP_ENTITIES.WIP_ENTITY_ID
Sales Order
MTL_SALES_ORDERS.SALES_ORDER_ID
 
 
ITEM_SEGMENT1 TO 20,
 
TRANSACTION_QTY,
 
TRANSACTION_UOM,
 
SUBINVENTORY_CODE,
 
ORGANIZATION_ID,
 
LOC_SEGMENT1 TO 20.
 
 
MTL_TRANSACTION_LOTS_INTERFACE:
 
TRANSACTION_INTERFACE_ID,
 
LOT_NUMBER,
 
LOT_EXPIRATION_DATE,
 
TRANSACTION_QUANTITY,
 
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
 
 
MTL_SERIAL_NUMBERS_INTERFACE:
 
TRANSACTION_INTERFACE_ID
FM_SERIAL_NUMBER,
 
TO_SERIAL_NUMBER,
 
VENDOR_SERIAL_NUMBER
 
R12 INVENTORY
 
 
Define Key Flexfields.
  1. System Items
  2. Item Categories
Setup Locations
  1. Define Locations
Setup Calendar
  1. Define Calendar
Units of Measure
  1. Define Units of Measure Classes
  2. Define Units of Measure
  3. Define Units of Measure Conversion
Inventory Organizations
  1. Define Organizations
  2. Define Organization Parameters
  3. Define Subinventories
  4. Define Stock Locators
Item Attributes
  1. Define Item Attribute Controls
Categories
  1. Define Category Codes
  2. Define Category Sets
  3. Define Default Category Sets
Status Codes
  1. Define Status Codes
Transactions
  1. Define Transaction Source Types
  2. Define Transaction Types
Profile Options
  1. Define Profile Options
 
RCV_TRANSACTIONS

 
It stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table. Once a row has been inserted into this table, it will never be updated.
When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction quantity does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing.
 

RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES

 
When a Ship Confirm is processed, one record is inserted in rcv_shipment_headers and one record is inserted in rcv_shipment_lines for each of the Sales Order Lines. 
The rcv_shipment_lines are linked to the one rcv_shipment_header record by shipment_header_id.
 
When a Shipment Line is received, the Receipt Number is populated in the rcv_shipment_headers record that was created for that Shipment.
Since only one rcv_shipment_headers record is created for each Ship Confirm process and the Receipt Number is also on rcv_shipment_headers record, there can only be one Receipt Number for a specific Shipment. 
 
For example:
 
1. Ship Confirm Sales Order Lines 1, 2, 3, 4, 5
a. The following records are created:
  • One rcv_shipment_header record
  • Five rcv_shipment_lines records
1.    What is item import? How is it done?
A)    The process of converting inventory items from another inventory system, migrating assembly and component items from a legacy manufacturing system, converting purchase items from a custom purchasing system and importing new items from a product data management package into Oracle Inventory. This import mechanism is achieved through a concurrent program called Open Item Interface.
                              Custom programs are executed prior to item interface and this gets data from the external systems into the interface tables such as MTL_SYSTEM_ITEMS _INTERFACE and MTL_ITEM_REVISIONS_INTERFACE. Item Interface program is then run which actually imports the items and revision information from the above mentioned interface face tables into the base tables such as MTL_SYSTEM_ITEMS_B, MTL_ITEM_REVISIONS. Item Interface assigns defaults and validates the data to ensure data integrity before feeding data into base tables.
2. What are Interface and Base Tables?
Interface Tables      :
MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (Oracle Defaults)
MTL_ITEM_CATEGORIES_INTERFACE (Oracle has Provided certain default categories, if not specified)
MTL_INTERFACE_ERRORS
Base Tables                   :
MTL_SYSTEM_ITEMS_B (Segment 1 stores Model # of Item)
MTL_ITEM_ATTRIBUTES,
MTL_ITEM_CATEGORIES,  
MTL_ITEM_LOCATIONS,  
MTL_ITEM_REVISIONS
MTL_ITEM_STATUS (Status Active/Engineer etc)
MTL_ITEM_SUB_INVENTORIES
3.  What are Item Attributes?
A)    Attributes are the specific characteristics associated to every item, namely order cost, item status, revision control, COGS account etc.
4.    What are Templates?
Templates are the defined set of attributes that can be used over and over to create similar items. Templates initial definition of items easier. Oracle has provided certain predefined templates such as (ATO MODEL, ATO OPTION CLASS, and FINISHED GOOD etc). Templates can also be User defined .
5)   What are Status Codes?
A)  Statuses are used to provide default values to certain item attributes to control the functionality of an item. Statuses typically default 8 item attributes namely,           BOM allowed, build in WIP, Customer orders enabled, internal orders enabled, invoice enabled, transactable, purchasable, stockable.
      Different status types include Active, Inactive, Engineer, obsolete, Phase-out, Prototype, OPM.
6.  What are Categories and Category Sets?
A) Category is a code used to group items with similar characteristics such as plastics, metals or glass items etc.
     A subset of categories grouped together is termed as a Category set. Typical category sets include purchasing, materials, costing and planning.
8.   What are Lot Numbers and Serial Numbers?
A)  Lot number is a number that identifies a specific batch of items.
     Serial Number is a number assigned to each unit of an item and used to track the item.
 9.  What are Locators?
A) A locator is a physical area with in the sub inventory where you store material such as a row, aisle, shelf, or a bin etc.
10.    What is a Sub Inventory?
It is a subdivision of an organization representing a physical area or a logical  grouping of items such as store room or a receiving dock.
11.      What are the flexfields in Inventory module?
A)  Item Key Flexfield and Category Key Flexfield.
12. While importing items from the legacy system through items interface what profile options do u set.
There are two profile options that we need to check, before running the Item Import. They are
i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure
ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status

Reports

July 15, 2011
Q: Name the report triggers.

A: 1. Before Parameter 2. After Parameter 3. Before Report 4. Between Pages 5. After Report

Q: What are bind parameter and lexical parameter used for?

A: A bind reference replaces a single value or expression.To create a bind reference in a query, prefix the parameter name with a colon (:). A lexical reference is a text string and can replace any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, or the ORDER BY clause. To create a lexical reference in a query, prefix the parameter name with an ampersand (&).

USER EXIT is of five types
1. FND SRWINIT
2. FND SRWEXIT
3. FND FORMAT_CURRENCY
4. FND FLEXIDVAL
5. FND FLEXSQL
What are Oracle Report and Flex field Report-Writing steps?
  1.   Define before report trigger
  2.   Define after report trigger
  3.   Define required parameters
  4.   Define other parameter
  5.   Call FND FLEXSQL to populate data field
  6.   Call FND FLEXSQL to populate other parameters
  7.   Define report query or queries
  8.   Create formula columns
  9.   Populate formula columns with FND FLEXIDVAL
  10.   Create default report layout
  11.   Finish Report
 

Differences between D2k Report and Apps Report
(1) in D2k Report there is no fecility to schedule the program where as in Apps report we have that facility
(2) in D2k Report we can’t use User-Exits where as we can use in Apps Report… to add dff values and diff currencies.
(3) when Compare to D2k Report in Apps report we can have more fecility to give out put in Fonts and formats.
Oracle apps reports are same as D2k reports.Only thing is they use exits(specifically defined for ERP) and use them in report. D2K reports and Oracle Reports basically have no difference the main difference between them is only that simple D2k report run in any environment  and we always use Tables if we want to run a report in D2K
But if we want to run any Oracle Report then we need an environment which is given by an pre-defined function “USER EXIT” which is used in Oracle. USER EXIT gives an environment for D2K Connect to Oracle APPS and if we use this function then we need to pass a parameter named “P_CONC_REQUEST_ID” in “FND SRWINIT” USER EXIT .

What are the User PARAMETERS in the Reports?
              P_CONC_REQUEST_ID
              P_FLEX_VALUE

What are User Exits? What are different types of User Exits in Oracle Apps? Explain the types.

User exit is a C subroutine written by user and called by Oracle Forms to do special-purpose processing. Helps the User to exit from the current working environment.

Types of User exit:

FND SRWINIT: Sets your profile option values and allows Oracle AOL user exits to detect that they have been called by an Oracle Reports program

FND SRWEXIT: Ensures that all the memory allocated for Oracle AOL user exits has been freed up properly.

FND FORMAT_CURRENCY: Is used for MULTIPLE CURRNECY REPORTING.


FND FLEXIDVAL: This API is used for get descriptions from flexfields get input from

FND FLEXSQL: this API is used for get ELECT/WHERE/HAVING/ORDER BY/GROUP BY from flex field tables.

 

 

How to Use XML Publisher to Generate Oracle Reports in Excel and PDF

The prerequisites to use XML Publisher include:
  • XML Data
  • Report Template
  • Registration of Data Definition and Template at XML Publisher

XML Publisher will use Oracle Reports for only the data query portion and will ignore the defined report layout

  • Log into Oracle with System Administrator Responsibility
  • Navigate to > Concurrent > Program > Define
  • Search for the desired Oracle Report
  • Change the Output Format from the default ‘Text’ to ‘XML’
  • Take special note of the Program Short Name (you’ll need it later to register the data at XML Publisher)

Download the XML Publisher Desktop Utility

The XML Publisher Desktop Utility is a plug-in to Microsoft Word. To create the RTF template, you must first load the XML Data File by clicking on the Data button and selecting Load XML Data. To get a sample XML data file, you can run the Oracle seeded report and download the output. Once the data file is loaded, you can make references to the XML Data elements.

You can test your RTF template with the loaded XML Data File by click on the Preview button and selecting one of the supported output formats. Once satisfied with the created RTF Template, the final steps is to register both the Data Definition and the Template at XML Publisher.

Register the Data Definition:
  • Log into Oracle with XML Publisher Administrator responsibility
  • Navigate to > Home > Data Definitions
  • Click on the Create Data Definition button
  • The Code must be the Concurrent Program Shortname from above
Register the Template
  • From Data Definitions, Navigate to > Templates
  • Click on the Create Template button
  • The Data Definition value should be the Data Definition you registered above
When the Customer runs this configured report through the Concurrent Manager, the Customer will follow the same steps to run the standard Oracle Report by selecting the report in the Single Request Screen and entering the desired run parameters. Now, the Customer has one additional option to select the desired output format of the report. From the Single Request Screen, click on the Options button and select the desired output.


1. Created query through Report Builder and saved as XXCNREC02.rdf

2. Registered Concurrent Program Executable and Define as XML output.
 

Executable : XXCNREC02
Short Name : XXCNREC02
Application: Application Object Library
Execution Method : Oracle Reports
Execution File Name : XXCNREC02
Definition window :
Program : Received/Consumption Detailed Report
Executable & Short Name : XXCNREC02
Method : Oracle Reports
Output : XML

3. Attached to the request group i.e., ‘SYSTEM ADMINISTRATOR REPORTS’.

4. Run through SRS , got output then save as XML type in local drive
ie., XXCNREC02_25678.xml

5. Through MS Word, Load XML Data and selected XML type file which is created in step 4.

6. Insert /Table Form then created layout.

7. Using ‘XML Publishing Administrator’ responsibility

Data Definition-> Create Data Definition
Name : Received/Consumption Details
Application : Application Object Library
Code : XXCNREC02
Start Date : 17-Jul-07
Templates -> Create Template
Name : Received/Consumption Details
Code : XXCNREC02
Application : Application Object Library
Data Definition: Received/Consumption Details
Type : RTF
File : Received/Consumption Details
Language : English
Territory : India

8. Run the report through SRS with System Administrator Responsibility.
 

select Upon Completion – > Options button – > Preview then
shows error.
WARNING
Executing request completion options…



R2R stands for Record 2 Report
 i.e. Record Jounal entries in the General Ledger and Report to the Financial Managers using reporting tools such as hyperion.
Below modules come under R2R cycle
1) General Ledger and below could be part of R2R cycle as well.


2) Financial Consolidation Hub


3) Enterprise Planning and Budgeting.


Q: How do you print barcode in the reports?

A: By installing the Barcode Font and using the Chart field in the Layout.

Q: What are the different sections in the Layout?

A: Header, Main, Trailer

Q: What is SRW package and some procedures in SRW?

A: It is the standard reports package and it has many procedures like USER_EXITS, DO_SQL, RUN_REPORT, MESSAGE,TRACE, BREAK, SET_ATTR.

Q: What are user exits in reports and name a few?

A: User exits provided a way to pass control from Reports Builder to a program you have written, which performs some function, and then returns control to Reports Builder. Ex: SRW.DO_SQL, SRW.USER_EXIT

Q: How do you display only one record on each page in a report?

A: Give Page Break in the Format trigger of the repeating frame.

Q: How do you write the report output to Excel file or text file?

A: 1.Use TEXT_IO package 2.Use SPOOL in After Report trigger 3.Use UTL Package

Q: Give an example of the implementation of “between pages trigger” in reports.

A: The total printed at the bottom of first page has to be carried to the top of the next page.

Q: Where in reports do you set the context information (like org_id)?

A: SRW.INIT

Q: What is Anchor in reports?

A: Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their positions relative to its parent.

Q: What is the difference between Conditional Formatting and format trigger?

A: Both provide the same functionality, used to format the output based on particular conditions. Format triggers provide a wide variety of options when compared to conditional formatting(GUI). In format Triggers we have the option to write PL/SQL code where as conditional formatting is GUI based which provide limited options.

Q: How do you call a concurrent program or another report from a report?

A: Use FND_SUBMIT.REQUEST() to call a concurrent program or a report. Use SRW.RUN_REPORT() to run a report directly without registering it as a concurrent program.

Q: How do you mail the output of a report?

A: 1. Use UTL_SMTP (refer to Scripts tab for more details) 2. Use MAILX called in a shell script registered as a concurrent program with parameters File name and path.

Q: How can you grey out/ highlight/hide some records based on conditions in a report?

A: Use Conditional formatting

 
Q: Q: Give an example of the implementation of “between pages trigger” in reports.
A: The total printed at the bottom of first page has to be carried to the top of the next page.


Q: What is Report Busting?

A: Reports bursting offers you to deliver a single report to multiple destinations simultaneously. It offers you to create multiple reports out of one single report model.

For example, you can create just one employee report for all your departments and send an email with a PDF-attachment containing the report to each manager. Each report would contain only the relevant department information for that particular manager. Using the reports bursting functionality will reduce the overhead since you will only have a single data fetch and report format.

Q: Is it possible to change the margins for oracle report?

A: Yes.



Q: How do I locate my output file from my report?
A: Perform the following steps:
1. Determine the Concurrent Request Number The first step to determining the file name is to locate the concurrent request number.
Navigate: Help=>View My Requests.
The Request ID field contains the concurrent request number.
2. Open a Telnet Session
a. You will need to open an MS-Dos prompt.
b. Select the Start button
c. Select Programs
d. Select Command Prompt
e. At the Dos prompt, enter the following command:
telnet <your server name>
3. Log into the Server
You will be prompted for your login and password for the Server.  Enter
these values.
4. Set your environment variables if necessary.  You may need to have your System Administrator perform this function for you.
5. Determine the Output File Directory

There are two environment variables that may be used to set the default output directory:

APPLCSF
APPLOUT
You can determine which is being used by “echoing” the above variables.
One of these will return a value.
APPLCSF is Applications Commonly Shared Files.
a. echo $APPLCSF
b. You MAY see a returned value, for example:
/oraclelogs/f1070161/app
c. There is usually an associated /out directory located off of the particular directory.  In that case, the output directory would be:
/oraclelogs/f1070161/app/out
d. You may access this directory by typing the following command:
cd $APPLCSF/out
e. Verify the directory name by typing:
pwd
f. Result:

/oraclelogs/f1070161/app/out
Next, try using APPLOUT.  This variable assumes that the value will be associated with a product top directory.
a. echo $APPLOUT
b. You MAY see a returned value:
out
c. In this instance, the directory /out is located off of the Product top (AP_TOP). To access this directory,
type the following command:
cd $AP_TOP/out
d. Verify the directory name by typing:
pwd
e. Result:
/u13/appl/f1070161/ap/8.0.159/out
If a variable did not return a value when echoed, it is not being used.


How do I determine the version of my report?
 
============================================================
A:  The report version can be determined from the operating system by running the following commands:
UNIX:
————–
Release 10.7:
cd $AP_TOP/srw
strings –a APXT7F99.rdf | grep Header
Release 11:
cd $AP_TOP/reports
strings -a AP7T7F99.rdf | grep Header
Win NT:
————–
Release 10.7:
cd %AP_TOP/srw
find /I “Header” APXT7F99.rdf
Release 11:
cd %AP_TOP/reports
find /I “Header” APXT7F99.rdf




D2K
Apps Reports
No User Exit
User Exit
P_Conc_Request_id not Required
P_CONC_REQUEST_ID Mandatory Parameter





What is Format Trigger? Format trigger is used to print conditionally printing of Layout. Format trigger return Boolean values.

Qns: What do you mean by Anchoring in Reports? With the help of Anchoring Relative position of two objects will be fixed.

Qns: What are Reports Trigger and Their Firing Sequence?

§         BEFORE PARAMETER FORM
§         AFTER PARAMETER FORM
§         BEFORE REPORT
§         BETWEEN PAGES
§         AFTER REPORT

Qns: What do you mean by Confine Mode in Reports?

Ans : Confine Mode Means Locking of Frames. If Confine Mode is on then you can not move the Object outside the Frame. If it is off you can move the objects.


Qns: Whats is Difference between Formula Column, Summary and Placeholder Column.
Ans:
§         Formula Column: Formula Column is used to compute Values. Formula Column always returns the Values.

§         Summary Column: Summary Column is used for Calculating summary function like Sum, Max. Min, Count.

§         Placeholder Columns: Placeholder Column is used to place the Variables.



Qns: Whats is Difference between and Lexical and Bind Parameter.
Ans:
Bind Parameter
Lexical Parameter
With the help of Bind Parameter You Can Pass Values.
With the help of Lexical Parameter You can pass string. You can pass lexical parameter in Select, From , Where Clause
Denoted by Colon
Denoted by Ampersand(&) sign


Qns: How will you Print Conditionally Layout in reports.
Ans: With the help of Format trigger.
 
For example code like:




Qns: How will you call Concurrent Program through Reports.
 
Qns: Whats are User Exit in Reports?
Qns: In Which Folder You will put the Rerort on the Server?
Qns : How will you Register the Reports?
Qns : What do you Mean by Token in Reports.
Qns : What are Various Default Type in Concurrent Programs?
Qns : What do you incompatibility in Concurrent Programs?
Qns: How will you Capture ORG_ID Dynamically in Concurrent Programs.
Qns : Suppose I have two Parameter Start Date and End Date and I want My Start Date should be lower then End Date. How will you Validate.
Qns : What are Various Execution Method in Concurrent Programs in executables.
Qns : What is Difference between Application Name Defined in Concurrent Program Executables and  Concurrent Programs.
Qns : Whats is Mandatory Parameter to Register PL/SQL Procedure in Apps?
Qns: How will you Call Reports with in Reports?
Qns: What do you Mean by Incompatibility in Reports?
Qns : What is significance of Mandatory Parameter in P_CONC_REQUEST_ID?
 
Qns: How will you put Message in Log File? 
Qns : How will you put Message of Reports in LOG Files.

What is Inventory
In simple terms it is a stock of items that your business is selling in order to make a living. Almost every non-IT related business has some physical items to sell and hence Inventory is at the heart of almost any business and is central to the planning process.
The considerations in setting it up are at least as complex as those for any other single application. Commonly, Inventory will be installed during the implementation of Order Entry, Purchasing, Manufacturing, or Supply Chain Management but it is an extremely complex module in its own right with several important setup considerations.
Inventory System: 
The simplest function of an Inventory system is to keep track of your items i.e. which item came in and which item went out of inventory and when the item count reaches below a certain threshold value then alert the responsible person to replenish it soon. So how does items go in and out of the warehouse? Let say your company manufactures office chairs. When a customer orders 5 chairs and when you ship them to the customer the count of the item goes down and when you manufacture them internally and when the finished chair is ready and moved to the warehouse the item count increases. An item can be internally consumed too.
An inventory system provides physical management of a company’s stock until it is either used up or sold. It also performs a logical, cataloging function. This stock is referred to by its item number  (usually item number is the segment1 column of mtl_system_items) and has many attributes that affect the transactions that the modules can process against the item.
Every other module within Oracle Applications that needs to name types of objects looks to the Inventory Item Master for details regarding an item. These objects include products ordered through Oracle Order Entry, items invoiced through Oracle Receivables, items purchased through Oracle Purchasing, items paid for through Oracle Payables, items maintained through Oracle Service, and items charged against projects in Oracle Projects.
Every module in the Manufacturing suite (Engineering, Bills of Material, Work in Process,  Master Production Schedule/Material Requirements Planning, and Cost Management) deals with items named in Inventory.
Inventory’s can also track intangibles like magazine subscriptions. The company can sell these items along with physical objects. Assemblies that are never stocked can also be defined in Inventory. A phantom item, for example, represents an intermediate step in manufacture, an assembly that is never stocked in inventory because it is used immediately in a higher-level assembly.
Oracle Inventory’s on-hand balance is increased when an item is bought or made. Inventory relinquishes control and decreases its balance when items are sold or put to their final use within the company. Pads of paper in the stock room are inventory; the same pad of paper on somebody’s desk is not-as far as the company is concerned, it is already used. A new desk in the warehouse belongs to inventory; a desk in somebody’s office is no longer in inventory. Many major purchases, such as plant and equipment, never have an on-hand balance because they are put to their final use
as soon as they are bought.
Inventory Tables:
MTL_CATEGORIES_B 
MTL_CATEGORIES_B is the code combinations table for item categories. Items are grouped into categories with in the context of a category set to provide flexible grouping schemes. The item category is a key flex field with a flex code of MCAT. 
The flexfield structure identifier is also stored in this table.MTL_CATEGORY_SETS_B
MTL_CATEGORY_SETS_B contains the entity definition for category sets. A category set is a categorization scheme for a group of items.Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes.

An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Only categories with the same flexfield structure may be grouped into a category set. 
CONTROL_LEVEL defines whether the category set is controlled at the item or the item/organization level. When an item is assigned to an item level category set within the item master organization, the category set assignment is propagated to all other organizations to which the item is assigned.
VALIDATE_FLAG defines whether a list of valid categories is used to validate category usage within the set. Validated category sets will not allow item assignment to the category set in categories that are not in a predefined list of valid categories. Category Sets now support multilingual category set name and description.
MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.

MTL_ITEM_CATEGORIES: 
MTL_ITEM_CATEGORIES stores the item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items may be assigned to multiple categories and category sets but may be assigned to only one category in a given category set. This table may be populated through the Master Items and Organization Items windows. It can also be populated by performing item assignments when a category set is defined. It is also populated when an item is transferred from engineering to manufacturing.

MTL_ITEM_SUB_INVENTORIES 

MTL_ITEM_SUB_INVENTORIES maintains a listing of subinventories assigned to an inventory or engineering item. These sub inventories make up the list of valid subinventories when transacting this specific item and the user has specified (in the master window) that the item must use subinventories restricted to a pre–defined list.
Oracle Inventory Interfaces
• Customer Item Interface
• Open Item Interface

 Open Replenishment Interface
• Open Transaction Interface
 Cycle Count Open Interface
• Reservations Open Interface 
• Move Orders Open Interface

§  Item import (Item conversion)
    The Item Interface lets you import items into Oracle Inventory. 

Pre-requisites:

1.    Creating an Organization
2.    Code Combinations
3.    Templates
4.    Defining Item Status Codes
5.    Defining Item Types
     

 Interface tables:          

1.                         MTL_SYSTEM_ITEMS_INTERFACE
2.                         MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
3.                         MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
4.                         MTL_INTERFACE_ERRORS (View errors after import)
    
Concurrent Program : =>                      Item import

In the item import parameters form, for the parameter ‘set process id’, specify the ‘set process id’ value given in the mtl_item_categories_interface table. The parameter ‘Create or Update’ can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.


Base Tables:             
1.    MTL_SYSTEM_ITEMS_B              
2.    MTL_ITEM_REVISIONS_B
3.    MTL_CATEGORIES_B
4.    MTL_CATEGORY_SETS_B
5.    MTL_ITEM_STATUS          
6.    MTL_ITEM_TEMPLATES





Validations:    
      
1.               Check for valid part_id/segment of the source table.
2.               Validate part_id/segment1 for master org.
3.               Validate and translate template id of the source table.
4.               Check for valid item type.
5.               Check for valid template id. (Attributes are already set for items, default attributes for that template      i.e., purchasable, stockable, etc )
6.               Check for valid item status.
7.               Validate primary uom of the source table.
8.               Validate attribute values.
9.               Validate other UOMs of the source table.
10.             Check for unique item type. Discard the item, if part has non-unique item type.
11.             Check for description, inv_um uniqueness
12.             Validate organization id.
13.             Load master records and category records only if all
14.             Load child record if no error found.




Some important columns that need to populated in the interface tables:

MTL_SYSTEM_ITEMS_INTERFACE:

PROCESS_FLAG = 1

(1= Pending,
2= Assign Complete,
3= Assign/Validation Failed,
4= Validation succeeded; Import failed,
5 = Import in Process,
7 = Import succeeded)


TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from  MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)



MTL_ITEM_CATEGORIES_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER.

ORGANIZATION_ID or ORGANIZATION_CODE or both.

TRANSACTION_TYPE = ‘CREATE’ (‘UPDATE’ or ‘DELETE’ is not possible through Item Import).

CATEGORY_SET_ID or CATEGORY_SET_NAME or both.

CATEGORY_ID or CATEGORY_NAME or both.

PROCESS_FLAG = 1

SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if
you are importing item and category assignment together)



MTL_ITEM_REVISIONS_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the

ORGANIZATION_ID or ORGANIZATION_CODE or both

REVISION

CHANGE_NOTICE

ECN_INITIATION_DATE

IMPLEMENTATION_DATE

IMPLEMENTED_SERIAL_NUMBER

EFFECTIVITY_DATE

ATTRIBUTE_CATEGORY

ATTRIBUTEn

REVISED_ITEM_SEQUENCE_ID

DESCRIPTION

PROCESS_FLAG = 1

TRANSACTION_TYPE = ‘CREATE’

SET_PROCESS_ID = 1

Each row in the mtl_item_revisions_interface table must have the REVISION and

EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
§  Inventory On-hand quantity Interface


This interface lets you import the on hand inventory into Oracle.
    

Interface tables:

MTL_TRANSACTIONS_INTERFACE

MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)

MTL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG.

Only records with TRANSACTION_MODE of 3, LOCK_FLAG of ’2′, and PROCESS_FLAG of ’1′ will be picked up by the Transaction Manager and assigned to a Transaction Worker.

If a record fails to process completely, then PROCESS_FLAG will be set to ’3′ and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:

MTL_ON_HAND_QUANTITIES

MTL_LOT_NUMBERS

MTL_SERIAL_NUMBERS

Validations:

Validate organization_id

Check if item is assigned to organization

Validate disposition_id

Check if the item for the org is lot controlled before inserting into the Lots interface table.

Check if the item for the org is serial controlled before inserting into Serial interface table.

Check if inventory already exists for that item in that org and for a lot.

Validate organization_id, organization_code.

Validate inventory item id.

Transaction period must be open.

Some important columns that need to be populated in the interface tables:

MTL_TRANSACTIONS_INTERFACE:

TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),

TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)

TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL –

If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,

TRANSACTION_TYPE_ID,

PROCESS_FLAG
(
         1 = Yet to be processed,
         2 = Processed,
         3= Error
)

TRANSACTION_MODE
 (
2 = Concurrent – to launch a dedicated transaction worker to explicitly process a set of transactions,
3 = Background – will be picked up by transaction manager polling process and assigned to
        transaction worker. These will not be picked up until the transaction manager is running
)

SOURCE_CODE,

SOURCE_HEADER_ID,

SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)

TRANSACTION_SOURCE_ID

Source Type
Foreign Key Reference
Account
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias
MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule
WIP_ENTITIES.WIP_ENTITY_ID
Sales Order
MTL_SALES_ORDERS.SALES_ORDER_ID


ITEM_SEGMENT1 TO 20,

TRANSACTION_QTY,

TRANSACTION_UOM,

SUBINVENTORY_CODE,

ORGANIZATION_ID,

LOC_SEGMENT1 TO 20.


MTL_TRANSACTION_LOTS_INTERFACE:

TRANSACTION_INTERFACE_ID,

LOT_NUMBER,

LOT_EXPIRATION_DATE,

TRANSACTION_QUANTITY,

SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)


MTL_SERIAL_NUMBERS_INTERFACE:

TRANSACTION_INTERFACE_ID
FM_SERIAL_NUMBER,

TO_SERIAL_NUMBER,

VENDOR_SERIAL_NUMBER

R12 INVENTORY


Define Key Flexfields.
1.    System Items
2.    Item Categories
Setup Locations
1.    Define Locations
Setup Calendar
1.    Define Calendar
Units of Measure
1.    Define Units of Measure Classes
2.    Define Units of Measure
3.    Define Units of Measure Conversion
Inventory Organizations
1.    Define Organizations
2.    Define Organization Parameters
3.    Define Subinventories
4.    Define Stock Locators
Item Attributes
1.    Define Item Attribute Controls
Categories
1.    Define Category Codes
2.    Define Category Sets
3.    Define Default Category Sets
Status Codes
1.    Define Status Codes
Transactions
1.    Define Transaction Source Types
2.    Define Transaction Types
Profile Options
1.    Define Profile Options

RCV_TRANSACTIONS


It stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table. Once a row has been inserted into this table, it will never be updated.
When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction quantity does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing.


RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES


When a Ship Confirm is processed, one record is inserted in rcv_shipment_headers and one record is inserted in rcv_shipment_lines for each of the Sales Order Lines. 
The rcv_shipment_lines are linked to the one rcv_shipment_header record by shipment_header_id.

When a Shipment Line is received, the Receipt Number is populated in the rcv_shipment_headers record that was created for that Shipment.
Since only one rcv_shipment_headers record is created for each Ship Confirm process and the Receipt Number is also on rcv_shipment_headers record, there can only be one Receipt Number for a specific Shipment. 

For example:

1. Ship Confirm Sales Order Lines 1, 2, 3, 4, 5
a. The following records are created:
·         One rcv_shipment_header record
·         Five rcv_shipment_lines records
1.    What is item import? How is it done?
A)    The process of converting inventory items from another inventory system, migrating assembly and component items from a legacy manufacturing system, converting purchase items from a custom purchasing system and importing new items from a product data management package into Oracle Inventory. This import mechanism is achieved through a concurrent program called Open Item Interface.
                              Custom programs are executed prior to item interface and this gets data from the external systems into the interface tables such as MTL_SYSTEM_ITEMS _INTERFACE and MTL_ITEM_REVISIONS_INTERFACE. Item Interface program is then run which actually imports the items and revision information from the above mentioned interface face tables into the base tables such as MTL_SYSTEM_ITEMS_B, MTL_ITEM_REVISIONS. Item Interface assigns defaults and validates the data to ensure data integrity before feeding data into base tables.
2. What are Interface and Base Tables?
Interface Tables      :
MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (Oracle Defaults)
MTL_ITEM_CATEGORIES_INTERFACE (Oracle has Provided certain default categories, if not specified)
MTL_INTERFACE_ERRORS
Base Tables                   :
MTL_SYSTEM_ITEMS_B (Segment 1 stores Model # of Item)
MTL_ITEM_ATTRIBUTES,
MTL_ITEM_CATEGORIES,  
MTL_ITEM_LOCATIONS,  
MTL_ITEM_REVISIONS
MTL_ITEM_STATUS (Status Active/Engineer etc)
MTL_ITEM_SUB_INVENTORIES
3.  What are Item Attributes?
A)    Attributes are the specific characteristics associated to every item, namely order cost, item status, revision control, COGS account etc.
4.    What are Templates?
Templates are the defined set of attributes that can be used over and over to create similar items. Templates initial definition of items easier. Oracle has provided certain predefined templates such as (ATO MODEL, ATO OPTION CLASS, and FINISHED GOOD etc). Templates can also be User defined .
5)   What are Status Codes?
A)  Statuses are used to provide default values to certain item attributes to control the functionality of an item. Statuses typically default 8 item attributes namely,           BOM allowed, build in WIP, Customer orders enabled, internal orders enabled, invoice enabled, transactable, purchasable, stockable.
      Different status types include Active, Inactive, Engineer, obsolete, Phase-out, Prototype, OPM.
6.  What are Categories and Category Sets?
A) Category is a code used to group items with similar characteristics such as plastics, metals or glass items etc.
     A subset of categories grouped together is termed as a Category set. Typical category sets include purchasing, materials, costing and planning.
8.   What are Lot Numbers and Serial Numbers?
A)  Lot number is a number that identifies a specific batch of items.
     Serial Number is a number assigned to each unit of an item and used to track the item.
 9.  What are Locators?
A) A locator is a physical area with in the sub inventory where you store material such as a row, aisle, shelf, or a bin etc.
10.    What is a Sub Inventory?
It is a subdivision of an organization representing a physical area or a logical  grouping of items such as store room or a receiving dock.
11.      What are the flexfields in Inventory module?
A)  Item Key Flexfield and Category Key Flexfield.
12. While importing items from the legacy system through items interface what profile options do u set.
There are two profile options that we need to check, before running the Item Import. They are
i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure
ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status

July 15, 2011
Open Interfaces in Oracle Applications for Data Migration
Oracle General Ledger
 Budget Upload (Refer to Uploading Budgets section of the Budgets chapter of the Oracle General Ledger User Guide)
 Importing Journals (Refer to Journal Import section of the Journals chapter of the Oracle General Ledger User Guide)
 Loading Daily Rates (Refer to Loading Daily Rates section of the Multi–Currency chapter of the Oracle General Ledger User Guide)
Oracle Payables

 Credit Card Transaction Interface  (Refer to Payable Credit Card Transactions Table appendix in the Oracle Payables User Guide).
 Invoice Import Interface. This interface is no longer supported for importing invoices. Use the Payables Open Interface instead. If you want to review column descriptions for the table, refer to the Invoice Import Interface Tables appendix in the Oracle Payables User’s Guide)
 Payables Open Interface (Refer to Payables Open Interface Tables appendix in the Oracle Payables User Guide)
 Purchase Order Matching (Refer to Purchase Order Matching Database Tables appendix in the Oracle Payables User Guide)
Oracle Receivables
 AutoInvoice (Refer to Importing Invoice Information Using AutoInvoice in the Oracle Receivables User Guide)
 AutoLockbox (Refer to Using AutoLockbox in the Oracle Receivables User Guide)
 Customer Interface (Refer to Customer Interface in the Oracle Receivables User Guide)
 Sales Tax Rate Interface (Refer to Importing Address Validation Data and Sales Tax Rates in the Oracle Receivables Tax Manual)
 Tax Vendor Extension (Refer to Implementing the Tax Vendor Extension in the Oracle Receivables Tax Manual)
Oracle Assets
 ACE Interface (Refer to About the ACE Interface section of the Oracle Assets User Guide)
 Budget Open Interface (Refer to Budget Open Interface section of the Oracle Assets User Guide)
 Mass Additions Interface (Refer to About the Mass Additions Interface section of the Oracle Assets User Guide)
 Production Interface (Refer to Using the Production Interface section of the Oracle Assets User Guide)
 Physical Inventory (Refer to Loading Physical Inventory Data section of the Oracle Assets User Guide)
Oracle Cash Management
 Bank Statement Open Interface (Refer to Bank Statement Open Interface section of the Oracle Cash Management User Guide)
 Forecasting Open Interface (Refer to Forecasting Open Interface section of the Oracle Cash Management User Guide)
 Reconciliation Open Interface (Refer to Reconciliation Open Interface section of the Oracle Cash Management User Guide)
Oracle Purchasing
 Requisitions Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
 Purchasing Documents Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
 Receiving Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
Oracle Inventory
 Customer Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
 Open Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
 Open Replenishment Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
 Open Transaction Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
 Cycle Count Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
 Reservations Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
 Move Orders Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
Oracle Projects
 Activity Mangement Gateway (Refer to Activity Management Gateway Technical Reference Manual)
 Client Extensions (Refer to Client Extensions in the Oracle Projects User Guide)
 Transaction Import (Refer to Intergrating Oracle Projects with non–Oracle Products in the Oracle Projects User Guide)
Interfaces and Conversions in Oracle Applications
Overview:
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. This article briefs you about some of the major interface programs.
Conversion/Interface Strategy:
1.    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.
2.            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. 
3.         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. 
4.         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.
Interface/Conversion examples with details:
            The below list of interfaces/conversions are covered in this section. Details like pre-requisites required, interface tables, interface program, base tables, validations that need to be performed after inserting the details into the interface tables and required columns that need to be populated in the interface table are discussed for each interface.
·         Order Import Interface (Sales Order Conversion)
·         Item import (Item conversion)
·         Inventory On-hand quantity Interface
·         Customer conversion
·         Auto Invoice Interface
·         AR Receipts
·         Lockbox Interface
·         AP Invoices
·         Vendor
·         Purchase Orders
·         Requisition
·         Receiving
·         Journal import
·         Budget import
·         Daily Conversion Rates
·         Order Import Interface (Sales Order Conversion)
Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.
            
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
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 table:
                    QP_PRICING_ATTRIBUTES
Concurrent Program:
                    Order Import
Validations:        

1.    Ordered_date should exist (header level)
2.    Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
3.    Delivery_lead_time should exist (line level)
4.    Earliest_acceptable_date should exist.
5.    Freight_terms should exist
6.    Check for sales_rep_id. Should exist for a booked order.
Notes: 
During import of orders, shipping tables are not populated.
If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.
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.
Some important columns that need to populated in the interface tables:
OE_HEADERS_IFACE_ALL:                    
ORIG_SYS_DOCUMENT_REF
ORDER_SOURCE
CONVERSION_RATE
ORG_ID
ORDER_TYPE_ID
PRICE_LIST
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID
SHIP_TO_ORG_ID
SHIP_FROM_ORG_ID
CUSTOMER_NAME
INVOICE_TO_ORG_ID
OPERATION_CODE
OE_LINES_IFACE_ALL
ORDER_SOURCE_ID
ORIG_SYS_DOCUMENT_REF
ORIG_SYS_LINE_REF
ORIG_SYS_SHIPMENT_REF
INVENTORY_ITEM_ID
LINK_TO_LINE_REF
REQUEST_DATE
DELIVERY_LEAD_TIME
DELIVERY_ID
ORDERED_QUANTITY
ORDER_QUANTITY_UOM
SHIPPING_QUANTITY
PRICING_QUANTITY
PRICING_QUANTITY_UOM
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID
INVOICE_TO_ ORG_ID
SHIP_TO_ORG_ID
PRICE_LIST_ID
PAYMENT_TERM_ID
§ Item import (Item conversion)
    The Item Interface lets you import items into Oracle Inventory. 
Pre-requisites:
Creating an Organization
Code Combinations
Templates
Defining Item Status Codes
Defining Item Types
Interface tables:         
                     MTL_SYSTEM_ITEMS_INTERFACE
                     MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
                     MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
                     MTL_INTERFACE_ERRORS (View errors after import)
Concurrent Program:
                     Item import
In the item import parameters form, for the parameter ‘set process id’, specify the ‘set process id’ value given in the mtl_item_categories_interface table. The parameter ‘Create or Update’ can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.
Base Tables: 
MTL_SYSTEM_ITEMS_B              
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS          
MTL_ITEM_TEMPLATES
Validations:    
           Check for valid item type.
           Check for valid part_id/segment of the source table.
           Validate part_id/segment1 for master org.
           Validate and translate template id of the source table.
           Check for valid template id. (Attributes are already set for items, default attributes for that 
            template, i.e., purchasable, stockable, etc )
           Check for valid item status.
           Validate primary uom of the source table.
           Validate attribute values.
           Validate other UOMs of the source table.
           Check for unique item type. Discard the item, if part has non-unique item type.
           Check for description, inv_um uniqueness
           Validate organization id.
           Load master records and category records only if all
           Load child record if no error found.
Some important columns that need to populated in the interface tables:
MTL_SYSTEM_ITEMS_INTERFACE:
PROCESS_FLAG = 1
(1= Pending,
2= Assign Complete,
3= Assign/Validation Failed,
4= Validation succeeded; Import failed,
5 = Import in Process,
7 = Import succeeded)
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from
MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from
MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)
MTL_ITEM_CATEGORIES_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER.
ORGANIZATION_ID or ORGANIZATION_CODE or both.
TRANSACTION_TYPE = ‘CREATE’ (‘UPDATE’ or ‘DELETE’ is not possible through Item Import).
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
CATEGORY_ID or CATEGORY_NAME or both.
PROCESS_FLAG = 1
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if
you are importing item and category assignment together)
MTL_ITEM_REVISIONS_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the
ORGANIZATION_ID or ORGANIZATION_CODE or both
REVISION
CHANGE_NOTICE
ECN_INITIATION_DATE
IMPLEMENTATION_DATE
IMPLEMENTED_SERIAL_NUMBER
EFFECTIVITY_DATE
ATTRIBUTE_CATEGORY
ATTRIBUTEn
REVISED_ITEM_SEQUENCE_ID
DESCRIPTION
PROCESS_FLAG = 1
TRANSACTION_TYPE = ‘CREATE’
SET_PROCESS_ID = 1
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
§  Inventory On-hand quantity Interface
This interface lets you import the on hand inventory into Oracle.
Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
MTL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:
Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG.
Only records with TRANSACTION_MODE of 3, LOCK_FLAG of ’2′, and PROCESS_FLAG of ’1′ will be picked up by the Transaction Manager and assigned to a Transaction Worker.
If a record fails to process completely, then PROCESS_FLAG will be set to ’3′ and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
Base Tables:
MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS
Validations:
Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Some important columns that need to be populated in the interface tables:
MTL_TRANSACTIONS_INTERFACE:
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL –
If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG
(
         1 = Yet to be processed,
         2 = Processed,
         3= Error
)
TRANSACTION_MODE
 (
2 = Concurrent – to launch a dedicated transaction worker to explicitly process a set of transactions,  
3 = Background – will be picked up by transaction manager polling process and assigned to 
        transaction worker. These will not be picked up until the transaction manager is running
)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Source Type
Foreign Key Reference
Account
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias
MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule
WIP_ENTITIES.WIP_ENTITY_ID
Sales Order
MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.
MTL_TRANSACTION_LOTS_INTERFACE:
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
MTL_SERIAL_NUMBERS_INTERFACE:
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER
§  Customer conversion
Customer Interface helps you create customers in Oracle Applications.
Interface tables:         
RA_CUSTOMERS_INTERFACE_ALL     
RA_CUSTOMER_PROFILES_INT_ALL              
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
Base tables:                
         RA_CUSTOMERS               
         RA_ADDRESSES_ALL
         RA_CUSTOMER_RELATIONSHIPS_ALL          
          RA_SITE_USES_ALL
Concurrent program:  
                                  Customer Interface
Validations:    
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:    
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
 Some important columns that need to be populated in the interface tables:
RA_CUSTOMERS_INTERFACE_ALL:
ORIG_SYSTEM_CUSTOMER_REF
SITE_USE_CODE
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG (I = Insert, U = Update)
CUSTOMER_NAME
CUSTOMER_NUMBER
CUSTOMER_STATUS
PRIMARY_SITE_USE_FLAG
LOCATION
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
STATE
PROVINCE
COUNTY
POSTAL_CODE
COUNTRY
CUSTOMER_ATTRIBUTE1
CUSTOMER_ATTRIBUTE2
CUSTOMER_ATTRIBUTE3
CUSTOMER_ATTRIBUTE4
CUSTOMER_ATTRIBUTE5
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATED_BY
CREATION_DATE
ORG_ID
CUSTOMER_NAME_PHONETIC
RA_CUSTOMER_PROFILES_INT_ALL:
INSERT_UPDATE_FLAG
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
CUSTOMER_PROFILE_CLASS_NAME
CREDIT_HOLD
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATION_DATE
CREATED_BY
ORG_ID
RA_CONTACT_PHONES_INT_ALL
ORIG_SYSTEM_CONTACT_REF
ORIG_SYSTEM_TELEPHONE_REF
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG
CONTACT_FIRST_NAME
CONTACT_LAST_NAME
CONTACT_TITLE
CONTACT_JOB_TITLE
TELEPHONE
TELEPHONE_EXTENSION
TELEPHONE_TYPE
TELEPHONE_AREA_CODE
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATION_DATE
CREATED_BY
EMAIL_ADDRESS
ORG_ID
Customer API
Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. These APIs utilize the new TCA model, inserting directly to the HZ tables.
API Details:
1.      Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2.      Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3.      Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
4.      Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
5.      Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
6.      Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
Base table:      
HZ_PARTIES                       
HZ_PARTY_SITES              
HZ_LOCATIONS
HZ_CUST_ACCOUNTS     
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL      
HZ_PARTY_SITE_USES
Validations:    
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:    
Validate cust_account_id, validate customer status.
 Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
§  Auto Invoice interface
This interface is used to import Customer invoices, Credit memos, Debit memos and On Account credits.
Pre-requisites:
            Set of Books
            Code combinations
            Items
            Sales representatives
            Customers
            Sales Tax rate
            Payment Terms
            Transaction Types
            Freight Carriers
            FOB
            Batch Sources
            Accounting Rules
Interface tables:         
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS (details about the failed records)
Base tables:    
          RA_BATCHES
          RA_CUSTOMER_TRX_ALL         
          RA_CUSTOMER_TRX_LINES_ALL        
          AR_PAYMENT_SCHEDULES_ALL        
          RA_CUSTOMER_TRX_LINE_SALESREPS        
          RA_CUST_TRX_GL_DIST_ALL
          RA_CUSTOMER_TRX_TYPES_ALL
Concurrent Program:  
                                Auto invoice master program
Validations:
Check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id,
Validate if the amount includes tax flag.
Some important columns that need to be populated in the interface tables:
           
RA_INTERFACE_LINES_ALL:
AGREEMENT_ID
COMMENTS
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1–15
HEADER_ATTRIBUTE_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SERVER_ORDER_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER

§  Receipt API
         To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.
Pre-requisites:
           
            Set of Books
            Code combinations
            Items
            Quick Codes
            Sales representatives
            Customers
            Sales Tax rate
API:
AR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY
Base tables:    
AR_CASH_RECEIPTS
Validations:    
Check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.
§  Lockbox interface
          AutoLockbox lets us automatically process receipts that are sent directly to the bank instead of manually feeding them in Oracle Receivables.
AutoLockbox is a three step process:
1. Import:
During this step, Lockbox reads and formats the data from your bank file into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader script.
2. Validation:
The validation program checks data in this interface table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL).
3. Post QuickCash:
This step applies the receipts and updates your customer’s balances.
Pre-Requisites:
            Banks
            Receipt Class
            Payment Method
            Receipt Source
            Lockbox
            Transmission format
            AutoCash Rule sets
Interface tables:         
AR_PAYMENTS_INTERFACE_ALL (Importdata from bank file)
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)
Base Tables: 
AR_CASH_RECEIPTS
AR_RECEIVABLES_APPLICATIONS                
AR_ADJUSTMENTS
AR_DISTRIBUTIONS_ALL
AR_PAYMENT_SCHEDULES_ALL
Concurrent program:
                    Lockbox
Validations:    
          Check for valid record type, transmission record id.
          Validate sum of the payments within the transmission.
          Identify the lockbox number (no given by a bank to identify a lockbox).
Some important columns that need to be populated in the interface tables:
AR_PAYMENTS_INTERFACE_ALL:
STATUS
RECORD_TYPE
LOCKBOX_NUMBER
BATCH_NAME
TRANSIT_ROUTING_NUMBER
ACCOUNT
CHECK_NUMBER
REMITTANCE_AMOUNT
DEPOSIT_DATE
ITEM_NUMBER
CURRENCY_CODE
DEPOSIT_TIME
§ AP invoice interface
This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.
Pre-requisites:
            Set of Books
            Code combinations
            Employees
            Lookups
Interface tables:         
AP_INVOICES_INTERFACE        
AP_INVOICE_LINES_INTERFACE
Base tables:                
AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info
Concurrent program:  
                          Payables Open Interface Import
           
Validations:    
                    Check for valid vendor
                    Check for Source, Location, org_id, currency_code’s validity
                    Check for valid vendor site code.
                    Check if record already exists in payables interface table.    
Some important columns that need to be populated in the interface tables:
           
AP_INVOICES_INTERFACE:
                        INVOICE_ID
                        INVOICE_NUM
                        INVOICE_DATE
                        VENDOR_NUM
                        VENDOR_SITE_ID
                        INVOICE_AMOUNT
                        INVOICE_CURRENCY_CODE
                        EXCHANGE_RATE
                        EXCHANGE_RATE_TYPE
                        EXCHANGE_DATE
                        DESCRIPTION
                        SOURCE
                        PO_NUMBER
                        PAYMENT_METHOD_LOOKUP_CODE
                        PAY_GROUP_LOOKUP_CODE
                        ATTRIBUTE1 TO 15
                        ORG_ID
AP_INVOICE_LINES_INTERFACE:
                       INVOICE_ID 
                       INVOICE_LINE_ID
                       LINE_TYPE_LOOKUP_CODE
                       AMOUNT
                       DESCRIPTION
                       TAX_CODE
                       PO_NUMBER
                        PO_LINE_NUMBER
                        PO_SHIPMENT_NUM
                        PO_DISTRIBUTION_NUM
                        PO_UNIT_OF_MEASURE
                        QUANTITY_INVOICED
                        DIST_CODE_CONCATENATED
                        DIST_CODE_COMBINATION_ID
                        ATTRIBUTE1
                        ATTRIBUTE2
                        ATTRIBUTE3
                        ATTRIBUTE4
                        ATTRIBUTE5
                        ORG_ID                    

§  Vendor conversion/interface
This interface is used to import suppliers, supplier sites and site contacts into Oracle applications.
Pre-requisites setup’s required:
Payment terms
Pay Groups
CCID
Supplier classifications
Bank Accounts
Employees (if employees have to set up as vendors)
Interface tables:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
Base Tables:
PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
Interface programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
Validations:
Check if vendor already exists
Check if vendor site already exists
Check if site contact already exists
Check if term is defined.
Some important columns that need to be populated in the interface tables:
AP_SUPPLIERS_INT:
VENDOR_NUMBER,
VENDOR_NAME,
VENDOR_TYPE,
STATE_REPORTABLE,
FED_REPORTABLE,
NUM_1099,
TYPE_1099,
PAY_GROUP_LOOKUP_CODE,
VENDOR_ID is auto generated.
AP_SUPPLIER_SITES_INT:
VENDOR_SITE_ID,
ORG_ID,
VENDOR_SITE_CODE,
INACTIVE_DATE,
PAY_SITE,
PURCHASING_SITE,
SITE_PAYMENT_TERM,
ADDRESS1,
ADDRESS2.
ADDRESS3,
CITY,
STATE,
COUNTRY,
ZIP,
PH_NUM,
FAX_NUMBER,
TAX_REPORTING_SITE_FLAG.
AP_SUP_SITE_CONTACTS_INT:
VENDOR_ID,
VENDOR_SITE_ID,
 FIRST_NAME,
LAST_NAME,
AREA_CODE,
PHONE,
EMAIL,
ORG_ID
§  Purchase Order conversion:
The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.
Import Standard Purchase Orders
Pre-requisites:
Suppliers, sites and contacts
Buyers
Line Types
Items
PO
Charge account setup
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_INTERFACE_ERRORS (Fallouts)
Interface Program:
Import Standard Purchase Orders.
Base Tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Validations:
Header:
Check if OU name is valid
Check if Supplier is valid
Check if Supplier site is valid
Check if buyer is valid
Check if Payment term is valid
Check if Bill to and ship to are valid
Check if FOB, freight terms are valid
Lines:
Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid
General:
Check for duplicate records in interface tables
Check if the record already exists in base tables.
Some important columns that need to be populated in the interface tables:
PO_HEADERS_INTERFACE:
INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL),
BATCH_ID,
ORG_ID,
INTERFACE_SOURCE_CODE,
ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’),
GROUP_CODE,
DOCUMENT_TYPE_CODE,
PO_HEADER_ID (NULL),
RELEASE_ID,
RELEASE_NUM,
CURRENCY_CODE,
RATE, AGENT_NAME,
VENDOR_ID,
VENDOR_SITE_ID,
SHIP_TO_LOCATION,
BILL_TO_LOCATION,
PAYMENT_TERMS
PO_LINES_INTERFACE:
INTERFACE_LINE_ID,
INTERFACE_HEADER_ID,
LINE_NUM,
SHIPMENT_NUM,
ITEM,
REQUISITION_LINE_ID,
UOM,
UNIT_PRICE,
FREIGHT_TERMS,
FOB
PO_DISTRIBUTIONS_INTERFACE:
INTERFACE_LINE_ID,
INTERFACE_HEADER_ID,
INTERFACE_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
QUANTITY_ORDERED,
QTY_DELIVERED,
QTY_BILLED,
QTY_CANCELLED,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
SET_OF_BOOKS,
CHARGE_ACCT,
AMOUNT_BILLED.
Import Blanket Purchase Agreements:
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
Interface program:
                         Import Price Catalogs
Base tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
Example:
Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1) Quantity = 500, price = 10, effective date from ’01-JAN-2006′ to
    ’31-JUN-2006′
2) Quantity = 500, price = 11, effective date from ’01-JUL-2006′ to
    ’01-JAN-2007′
To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE
LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’
All the line-level records above must have the same INTERFACE_HEADER_ID.
§  Requisition import
You can automatically import requisitions into Oracle Applications using the Requisitions Open Interface
Pre-requisites:
            Set of Books
            Code combinations
            Employees
            Items
            Define a Requisition Import Group-By method in the Options window.
            Associate a customer with your deliver-to location using the Customer Addresses window for  
             internally sourced requisitions.
Interface tables:         
                     PO_REQUISITIONS_INTERFACE_ALL
                     PO_REQ_DIST_INTERFACE_ALL                      
           
Base tables:                
PO_REQUISITIONS_HEADERS_ALL                                                                
PO_REQUISITION_LINES_ALL              
PO_REQ_DISTRIBUTIONS_ALL
Concurrent program: 
                           REQUISITION IMPORT
            Validations:    
                    Check for interface transaction source code, requisition destination type.
                    Check for quantity ordered, authorization status type.
Some important columns that need to be populated in the interface tables:
PO_REQUISITIONS_INTERFACE_ALL:
INTERFACE_SOURCE_CODE (to identify the source of your Requisitions)
DESTINATION_TYPE_CODE
AUTHORIZATION_STATUS
PREPARER_ID or PREPARER_NAME
QUANTITY
CHARGE_ACCOUNT_ID or charge account segment values
DESTINATION_ORGANIZATION_ID or DESTINATION_ORGANIZATION_CODE
DELIVER_TO_LOCATION_ID or DELIVER_TO_LOCATION_CODE
DELIVER_TO_REQUESTOR_ID or DELIVER_TO_REQUESTOR_NAME
ORG_ID
ITEM_ID or item segment values (values if the SOURCE_TYPE_CODE or
DESTINATION_TYPE_CODE is ‘INVENTORY’)
PO_REQ_DIST_INTERFACE_ALL:
CHARGE_ACCOUNT_ID or charge account segment values
DISTRIBUTION_NUMBER
DESTINATION_ORGANIZATION_ID
DESTINATION_TYPE_CODE
INTERFACE_SOURCE_CODE
ORG_ID
DIST_SEQUENCE_ID (if MULTI_DISTRIBUTIONS is set to Y)
·         PO Receipts Interface
The Receiving Open Interface is used for processing and validating receipt data that comes from sources other than the Receipts window in Purchasing.
Pre-requisites:
            Set of Books
            Code combinations
            Employees
            Items
Interface tables:
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
PO_INTERFACE_ERRORS           
Concurrent program:
RECEIVING OPEN INTERFACE
Base tables:
                     RCV_SHIPMENT_HEADERS
                     RCV_SHIPMENT_LINES
                     RCV_TRANSACTIONS
Validations:
Check that SHIPPED_DATE should not be later than today.
Check if vendor is valid.
If Invoice number is passed, check for its validity
Check if Item is valid
Some important columns that need to be populated in the interface tables:
RCV_HEADERS_INTERFACE:
HEADER_INTERFACE_ID
GROUP_ID
PROCESSING_STATUS_
CODE
RECEIPT_SOURCE_CODE
TRANSACTION_TYPE
SHIPMENT_NUM
RECEIPT_NUM
VENDOR_NAME
SHIP_TO_
ORGANIZATION_CODE
SHIPPED_DATE
INVOICE_NUM
INVOICE_DATE
TOTAL_INVOICE_
AMOUNT
PAYMENT_TERMS_ID
EMPLOYEE_NAME
VALIDATION_FLAG (Indicates whether to validate a row or not, values ‘Y’, ‘N’)
RCV_TRANSACTIONS_INTERFACE:
INTERFACE_TRANSACTION_ID
GROUP_ID
TRANSACTION_TYPE (’SHIP’ for a standard shipment (an ASN or ASBN) or ’RECEIVE’ for a standard receipt)
TRANSACTION_DATE
PROCESSING_STATUS_CODE =’PENDING’
CATEGORY_ID
QUANTITY
UNIT_OF_MEASURE
ITEM_DESCRIPTION
ITEM_REVISION
EMPLOYEE_ID
AUTO_TRANSACT_CODE
SHIP_TO_LOCATION_ID
RECEIPT_SOURCE_CODE
TO_ORGANIZATION_CODE
SOURCE_DOCUMENT_CODE
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
SUBINVENTORY
HEADER_INTERFACE_ID
DELIVER_TO_PERSON_NAME
DELIVER_TO_LOCATION_CODE
VALIDATION_FLAG
ITEM_NUM
VENDOR_ITEM_NUM
VENDOR_ID
VENDOR_SITE_ID
ITEM_ID
ITEM_DESCRIPTION
SHIP_TO_LOCATION_ID
§  GL Journal interface
This interface lets you import journals from other applications like Receivables, Payables etc to integrate the information with General Ledger.
Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations
Currencies
Categories
Journal Sources
Interface tables:         
GL_INTERFACE
Base tables:                
GL_JE_HEADERS   
GL_JE_LINES          
GL_JE_BACTHES
Concurrent Program:  
Journal Import
Journal Posting — populates GL_BALANCES
Validations:    
Validate SOB, journal source name, journal category name, actual flag
                        A – Actual amounts
                        B – Budget amounts
                        E – Encumbrance amount
If you enter E in the interface table, then enter appropriate encumbrance ID, if B enter budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if code combination is valid and enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.
Some important columns that need to be populated in the interface tables:
GL_INTERFACE:
             STATUS                       
              SET_OF_BOOKS_ID              
             ACCOUNTING_DATE              
             CURRENCY_CODE                
             DATE_CREATED                 
             CREATED_BY                   
             ACTUAL_FLAG                  
             USER_JE_CATEGORY_NAME        
             USER_JE_SOURCE_NAME          
             CURRENCY_CONVERSION_DATE     
             ENCUMBRANCE_TYPE_ID          
             BUDGET_VERSION_ID            
             USER_CURRENCY_CONVERSION_TYPE
             CURRENCY_CONVERSION_RATE     
             SEGMENT1 to
             ENTERED_DR                   
             ENTERED_CR                   
             ACCOUNTED_DR                 
             ACCOUNTED_CR                 
             TRANSACTION_DATE             
             PERIOD_NAME                  
             JE_LINE_NUM                  
             CHART_OF_ACCOUNTS_ID         
             FUNCTIONAL_CURRENCY_CODE     
             CODE_COMBINATION_ID          
             DATE_CREATED_IN_GL           
             GROUP_ID        
§  GL budget interface
Budget interface lets you load budget data from external sources into Oracle Applications.

Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations

Interface tables:       
GL_BUDGET_INTERFACE

Base tables:                
GL_BUDGETS         
GL_BUDGET_ASSIGNMENTS     
GL_BUDGET_TYPES

Concurrent program:
                                    Budget Upload          

Validations:
                     Check if CURRENCY_CODE is valid.
                     Check if SET_OF_BOOKS_ID is valid.
                     Check if BUDGET_ENTITY_NAME (budget organization) is valid.

Some important columns that need to be populated in the interface tables:
GL_BUDGET_INTERFACE:
BUDGET_NAME NOT
BUDGET_ENTITY_NAME
CURRENCY_CODE
FISCAL_YEAR
UPDATE_LOGIC_TYPE
BUDGET_ENTITY_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
BUDGET_VERSION_ID
PERIOD_TYPE
DR_FLAG
STATUS
ACCOUNT_TYPE
PERIOD1_AMOUNT through PERIOD60_AMOUNT
SEGMENT1 through SEGMENT30
·         GL daily conversion rates
This interface lets you load the rates automatically into General Ledger.

Pre-requisites:
            Currencies
            Conversion rate Types

Interface tables:
GL_DAILY_RATES_INTERFACE

Base tables:
GL_DAILY_RATES            
GL_DAILY_CONVERSION_TYPES

Concurrent Program:
You do not need to run any import programs. The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.

Validations:
Check ifFROM_CURRENCY and TO_CURRENCY are valid.
Check if USER_CONVERSION_TYPE is valid.

Some important columns that need to be populated in the interface tables:

GL_DAILY_RATES_INTERFACE:
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG (D= Delete, I = Insert, U = Update)
INVERSE_CONVERSION_RATE


No comments:

Post a Comment