, , , , ,

Transaction/Journal Migration

The migration of transactions & journals from legacy to oracle is the most difficult part of data migration. You should migrate all the GL balance for the current year periods, all the last year periods and the ending balance of ‘Y-2’ to oracle. You need to do a proper reconciliation of GL balances after migration of open AP/AR invoices to the current period.
AP Open Invoice
The charge and tax amounts of the open AP invoices already exist in the legacy system. You need to make sure that you don’t account them again in oracle.
Accounting entries in Legacy
Charge A/C Dr
Tax  A/C Dr                            Supplier Liability A/C Cr
Instead of the liability account use a clearing account(Liability) while transferring the journals to oracle GL. Make the below changes while transferring these journals to oracle GL
Charge A/C Dr
Tax  A/C Dr                            Migration Clearing A/C (Liability) Cr
When you transfer the AP invoice from legacy to oracle. Use the above migration clearing account as charge/tax/freight A/C
Accounts created in oracle AP
Migration Clearing A/C (charge)
Migration Clearing A/C(tax)                       Supplier Liability A/C Cr
Transfer these accounts from AP to Oracle GL. After posting all these journals in the current period the YTD balance of migration clearing A/C should be zero.
AR Open Invoice
Similar to AP open invoice in AR invoice we use a clearing account (ASSET) instead of the receivable A/C in legacy. The revenue and tax amounts of the open AR invoices exist in the legacy system. You need to make sure that you don’t account them again in oracle.
Accounting entries in Legacy
Receivable A/C Dr
                                    Revenue A/C Cr                           
                                    Tax A/C Cr            
              
Make the below changes while transferring these journals to oracle GL
Migration Clearing A/C (Asset) Dr
                                                             Revenue A/C Cr                           
                                                              Tax A/C Cr           
When you transfer the AR invoice from legacy to oracle. Use the above migration clearing account as revenue/tax/freight A/C (use transaction type in auto accounting and put the clearing accounts in transaction type)
Accounts created in oracle AR
Receivable A/C Dr
                                    Migration Clearing A/C (Revenue) Cr                           
                                     Migration Clearing A/C (Tax) Cr            
Transfer these accounts from AR to Oracle GL. After posting all these journals in the current period the YTD balance of migration clearing A/C should be zero.
Fixed Assets
Transfer all the assets from legacy to oracle with Accumulated depreciation till the current period. Oracle ‘ll not create any depreciation for previous period. System ‘ll create below journal entries when you close & transfer the journals for the first asset period
Addition Journals
Addition Cost A/C Dr.
                                  Addition Cost Clearing A/C  Cr.
All these assets are present in legacy GL and transferred as a part of GL migration. You need to reverse all these journals in current period
Depreciation Journals
System‘ll create the depreciation journals for the current period.
All the past period depreciation & acc. Depreciation amounts are transferred through GL migration.
/by
, , , ,

Oracle Data & Migration

The sets of data required by Oracle Applications can be broadly classified in to 4 categories

  • Master Data : Item, Customer, Vendor, Bank Accounts, etc.
  • Opening Balances: On-Hand Quantity of Items, GL Balances, etc.
  • Open Transactions : Open Purchase Orders, Open Sales Order, Open Invoice etc.
  • Set-up Data :  Item Categories, Stock Locators

Similary source of data can be classified as

  • Existing Applications : DB2, Tally, SMS, etc.
  • Electronic Data : Comming through EDI in formats of Excel, Word, etc.
  • Hard Copy Data : In Registers
  • Non – Existent : Equipment Master, Collect and then migrate

The above diagram depicts the high level Data Migration approach from Legacy to ORACLE.
At a minimum, the data migration from Legacy to ORACLE consists of these tasks:

  • Extracting the data from Legacy data base.
  • Loading the extracted data into staging area.
  • Mapping and transformation of source data.
  • Migrating source data from staging area into target data base.
/by
, , , ,

Important APIs/Open Interfaces

Customer APIs
hz_party_v2pub.create_organization
Org Contact Role Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility
Vendor APIs
Procedure : AP_PO_VENDORS_APIS_PKG.INSERT_NEW_VENDOR
Procedure=> AP_PO_VENDORS_APIS_PKG.INSERT_NEW_VENDOR_SITE
Procedure=> AP_PO_VENDORS_APIS_PKG.INSERT_NEW_VENDOR_CONTACT
Item Import
You must import items into the Item Master organization before you import items into additional organizations. You can accomplish this by specifying only your Item Master organization on a first pass run of the Item Interface. Once this has completed, you can run the Item Interface again, this time specifying an additional or all organizations.
Populate these tables first..
MTL_SYSTEM_ITEM_INTERFACE
MTL_ITEMS_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
Always set the TRANSACTION_TYPE column to CREATE, to create an item record (true when both importing a new item and assigning an already existing item to another organization). This is the only value currently supported by the Item Interface.Set the PROCESS_FLAG to 1 (Pending), so that the Item Interface can pick up the row and process it.The program sets the PROCESS_FLAG to 7 (Import succeeded) or  4 (Import failed) or  3 (validation failed). A row is inserted into the MTL_INTERFACE_ERRORS table for all failed rows.When the Item Interface imports an item, it also assigns the item to the mandatory category sets based on the item defining attributes. The default category for each category set is used. The Item Interface also allows you to assign items to other category sets and categories, when there is data for item category assignments in the MTL_ITEM_CATEGORIES_INTERFACE table.
Run Import Items Report :
Indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored.Parameter Create or Update Items..
1 Create new items.
2 Update existing items.
/by
, , ,

Data Migration

Data Migration is the process of transferring data between one system to another system. Data migration is required when organizations or individuals change computer systems or upgrade to new systems, or when systems merge (such as when the organizations that use them undergo a merger/takeover).
To achieve an effective data migration in oracle, data on the old system is mapped to the oracle database format. Data migration may involve many phases but it minimally includes data extraction where data is read from the old system and data loading where data is written to the oracle system.
After loading into Oracle, results are subjected to data verification to determine whether data was accurately translated, is complete, and supports processes in the new system. During verification, there may be a need for a parallel run of both systems to identify areas of disparity and forestall erroneous data loss.
Automated and manual data cleaning is commonly performed in migration to improve data quality, eliminate redundant or obsolete information, and match the requirements of the new system. Data migration phases (Design, Extraction, Cleansing, Load, Verification) for applications of moderate to high complexity are commonly repeated several times before the new system is deployed.
Data conversion is a bit different from data migration in the sense that in data conversion we convert data form one format to another format in the different version of the same system. Example: When we upgrade from Oracle 11i to Oracle R12 we do a data conversion from 11i to R12

Interface is similar to both the data migration and conversion in the sense it involves the movement of data from one system to another system but its requirement in business is completely different. Interfaces are written to transfer data on a regular basis where as most of the data migration and conversion job are one time activity.
Following examples distinguishes all the above three process

  • Importing customer / supplier / item  information from legacy – Data Migration
  • Moving customer / supplier / item information from 11i to R12 – Data Conversion
  • Importing/Exporting PO/SO/WIP information from one system to oracle on a daily basis – Interface Job
/by
, , , , , , ,

Oracle APIs and Open Interfaces

Oracle Puchasing:
1. Requisitions Open Interface
2. Purchasing Documents Open Interface
3. Cancel PO APIs
4. Receiving Open Interface
Oracle Inventory:
1. Open Transaction Interface
2.1 Customer Item Interface
2.2 Open Item Interface
2.3 Cycle Count Open Interface
3.1 Open Replenishment Interface
3.2 Reservations Open Interface
3.3 Move Orders Open Interface
OM:
1.    Order Import
2.    Process Order API
3.    RLM Open Interfaces
Actions, APIs, and Parameters: Descriptions of the APIs used for various functions and the API parameters.
Application Parameter Initialization: Description of the application parameter initialization call.
Trip API: Create and update trip records and perform actions on trips.
Stop API: Create and update stop records and perform actions on stops.
Deliveries API: Create and update trip stop records and perform actions on trip stops.
Delivery Details API: Assign and unassign delivery details to and from deliveries, split a delivery detail, update a delivery detail with new
information, and create trips and deliveries for multiple delivery lines.
Container API: Create container records, update container records, autopack containers, perform actions on containers.
Freight Cost APIs: Create freight cost records, update freight cost records, validate freight cost types, delete freight cost records.
Tables
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
WSH_DELIVERY_DETAILS
OE_ORDER_HOLDS_ALL
OE_PRICE_ADJUSTMENTS
OE_TRANSACTION_TYPES_ALL
OE_DROP_SHIP_SOURCES
OE_SETS
OE_SYSTEM_PARAMETSR
MTL_DEMANDS
MTL_RESRVATIONS
Inventory
Open Transaction Interface
Oracle Inventory provides an open interface for you to load transactions from external applications and feeder systems. These transactions could include sales order shipment transactions from an Order Management system other than Oracle Order Management, or they could be simple material issues, receipts, or transfers loaded from data collection devices. The following transaction types are supported by this interface:
• Inventory issues and receipts (including user-defined transaction types)
• Subinventory transfers
• Direct interorganization transfers
• Intransit shipments
• WIP component issues and returns
• WIP assembly completions and returns
• Sales order shipments
• Inventory average cost updates
• LPN Pack
• Unpack
• Split Transactions
• Inventory Lot Split/ Merge/ Translate Transactions
This interface is also used as an integration point with Oracle Order Management for shipment transactions. Oracle Order Management’s Inventory Interface program populates the interface tables with transactions submitted through the Confirm
Shipments window.
You must write the load program that inserts a single row for each transaction into the MTL_TRANSACTIONS_INTERFACE table. For material movement of items that are under lot or serial control, you must also insert rows into MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE respectively. If you insert WIP
assembly/completion transactions that complete or return job assemblies, you must also insert rows into the CST_COMP_SNAP_ INTERFACE table if the organization referenced uses average costing. The system uses this information to calculate completion cost.
There are two modes you can use to process your transactions through the interface. In the first processing mode, you populate the interface table only. Then the Transaction Manager polls the interface table asynchronously looking for transactions to process, groups the transaction rows, and launches a Transaction Worker to process each group.
In the second processing mode, you insert the rows in the interface table and call a Transaction Worker directly, passing the group identifier of the interfaced transactions as a parameter so that the worker can recognize which subset of transactions to process.
The Transaction Worker calls the Transaction Validator, which validates the row, updates the error code and explanation if a validation or processing error occurs, and derives or defaults any additional columns. Next, the Transaction Processor records the transaction details in the transaction history table along with relevant current cost information. All material movement transactions update inventory perpetual balances for the issue, receipt, or transfer locations. Once the transaction has been successfully processed, the corresponding row is deleted from the interface table. Finally, the transaction is costed by the transaction cost processor, which runs periodically, picking up all transactions from the history table that have not yet been marked as costed.
Open Replenishment Interface
Oracle Inventory provides an open interface for you to easily load replenishment requests from external systems such as a barcode application. Such requests may be in the form of stock-take counts or requisition requests for subinventories in which you do not track quantities.
Cycle Count Entries Interface
You can import cycle count entries from an external system into Oracle Inventory using the Cycle Count Entries Interface. This interface validates all data that you import into Oracle Inventory. It also performs foreign key validation and checks for attribute inter-dependencies, acceptable values, and value ranges. The interface ensures that the imported cycle count entries contain the same detail as items entered manually using the Cycle Count Entries window. Errors detected during validation are written to the Cycle Count Interface Errors table.
Kanban Application Program Interface
The Kanban API is a public API that allows you to update the supply status of kanban cards. To accomplish this task, you use the public procedure update_card_supply_status
Item Open Interface
You can import items from any source into Oracle Inventory and Oracle Engineering using the Item Open Interface. With this interface, you can convert inventory items from another inventory system, migrate assembly and component items from a legacy manufacturing system, convert purchased items from a custom purchasing system, and import new items from a product data management package. The Item Open Interface validates your data, ensuring that your imported items contain the same item detail as items that you enter manually in the Master Item window.
You can also import item category assignments. This can be done simultaneously with a process of importing items, or as a separate task of importing item category assignments only. For this purpose, the Inventory menu contains the Import submenu with the Import Items and Import Item Category Assignments menu entries.
Receiving Open Interface
You use the Receiving Open Interface to process and validate receipt data that comes from sources other than the Receipts window in Oracle Purchasing. These sources include:
• Receipt information from other Oracle applications or legacy systems
• Brocades and other receiving information from scanners and radio frequency devices
• Advance Shipment Notices (ASNs) from suppliers
The Receiving Open Interface maintains the integrity of the new data as well as the receipt data that resides in Oracle Purchasing.
The Receiving Open Interface does not support:
• Movement statistics
• Dynamic locators

BOM
Bills of Materials Open Interfaces
WIP
Open Move Transaction Interface
You can load Move transaction information into the Open Move Transaction Interface from a variety of sources, including external data collection devices such as bar code readers, automated test equipment, cell controllers, and other manufacturing execution systems. You then use the interface to load these transactions into Oracle Work in Process. All transactions are validated and invalid transactions are marked, so that you can correct and resubmit them.
Open Resource Transaction Interface
You can use external data collection devices such as bar code readers, payroll systems, and time card entry forms to collect resource and overhead transaction data, then load the data into the Open Resource Transaction Interface for Oracle Work in Process to process.
Work Order Interface
The Work Order Interface enables you to import Discrete job and Repetitive schedule header information, and Discrete job operations, material, resource, and scheduling information from any source, using a single process.
You can import:
• Planned orders for new Discrete jobs,
• Discrete job operations, components, resources, resource usage, and scheduling details
• Update and reschedule recommendations for existing Discrete jobs
• Suggested Repetitive schedules Work in Process then uses this information to automatically create new Discrete jobs
and pending Repetitive Schedules, or to update existing Discrete jobs.
MRP
Open Forecast Interface
You can import forecasts from any source using the Open Forecast Interface table. Oracle Master Scheduling/MRP automatically validates and implements imported forecasts as new forecasts in Oracle Master Scheduling/MRP.
Cost Management
Periodic Cost Open Interface
The Oracle Periodic Cost Open Interface provides an open interface for you to easily load periodic item costs from external applications or legacy systems and migrate them into the Oracle Cost Management Application. This interface should only be used to bring in periodic costs for the first opened periodic period. It cannot be used for subsequent periods. Costs in subsequent periods are calculated by the system.
Cost Import Interface
The Oracle Cost Import Interface enables you to import costs for items from legacy systems, and import new cost information for existing items. Importing resource costs and resource overhead rates is also supported. You will also be able to replace existing cost information with the new cost information. However, updating of existing costs is not supported. Importing costs into frozen cost type is also not supported. The item costs will have to be imported into another cost type and then the cost update may be run to update the frozen cost type
OM
Order Import
Pricing Open interface
Pick release
/by