data conversions - hand book

DATA CONVERSIONS – HAND BOOK

Data Conversions – Hand Book

ContentsDocument Control....................................................................................................7Introduction............................................................................................................8Item Master.............................................................................................................9

Conversion Short Description..................................................................................................................................10Data Category:.........................................................................................................................................................10Interface Tables.......................................................................................................................................................10Concurrent Program / / / /APIs................................................................................................................................10Parameters................................................................................................................................................................10Setups/Pre-requisites................................................................................................................................................12Process Flow Diagram.............................................................................................................................................13Different ways of using the program / API.............................................................................................................14Field Mapping details..............................................................................................................................................16Validation Logic......................................................................................................................................................16Errors and Solutions................................................................................................................................................17Data Validation Techniques....................................................................................................................................18Tuning Methods.......................................................................................................................................................18Appendix A – Links to Oracle Documentation.......................................................................................................18Appendix A – Definitions........................................................................................................................................20

INSTALLBASE.........................................................................................................22Conversion Short Description..................................................................................................................................23Data Category:.........................................................................................................................................................23Interface Tables.......................................................................................................................................................23Base Tables..............................................................................................................................................................23Concurrent Program / /APIs....................................................................................................................................23Parameters................................................................................................................................................................23Setups/Pre-requisites................................................................................................................................................24Process Flow Diagram.............................................................................................................................................25Ways of using the program / API............................................................................................................................27Field Mapping details..............................................................................................................................................29Validation Logic......................................................................................................................................................29Errors and Solutions................................................................................................................................................29Data Validation Techniques....................................................................................................................................31Tuning Methods.......................................................................................................................................................31Appendix A – Links to Oracle Documentation.......................................................................................................31Appendix A – Definitions........................................................................................................................................33

INVENTORY TRANSACTIONS....................................................................................34Conversion Short Description..................................................................................................................................35Data Category:.........................................................................................................................................................35Interface Tables.......................................................................................................................................................35Base Tables..............................................................................................................................................................36Concurrent Program / /APIs....................................................................................................................................36Parameters................................................................................................................................................................36Setups/Pre-requisites................................................................................................................................................36Process Flow Diagram.............................................................................................................................................37Ways of using the program / API............................................................................................................................39Field Mapping details..............................................................................................................................................41Validation Logic......................................................................................................................................................42Errors and Solutions................................................................................................................................................42Data Validation Techniques....................................................................................................................................43


Tuning Methods.......................................................................................................................................................43Appendix A – Links to Oracle Documentation.......................................................................................................43Appendix A – Definitions........................................................................................................................................44

Customer Master...................................................................................................45Conversion Short Description..................................................................................................................................46Data Category:.........................................................................................................................................................46Interface Tables.......................................................................................................................................................46Base Tables..............................................................................................................................................................46Error Tables.............................................................................................................................................................47Concurrent Program / /APIs....................................................................................................................................47Parameters................................................................................................................................................................47APIs Involved..........................................................................................................................................................47Setups/Pre-requisites................................................................................................................................................48Process Flow Diagram.............................................................................................................................................49Field Mapping details..............................................................................................................................................50Validation Logic......................................................................................................................................................52Errors and Solutions................................................................................................................................................60

PO Vendors............................................................................................................62Conversion Short Description..................................................................................................................................63Data Category..........................................................................................................................................................63Interface Tables.......................................................................................................................................................63Concurrent Program / /APIs....................................................................................................................................63Parameters................................................................................................................................................................63Setups/Pre-requisites................................................................................................................................................64Process Flow Diagram.............................................................................................................................................65Different scenarios of using the program / API.......................................................................................................67Field Mapping details..............................................................................................................................................70Validation Logic......................................................................................................................................................70Errors and Solutions................................................................................................................................................71Data Validation Techniques....................................................................................................................................72Tuning Methods.......................................................................................................................................................72Appendix A – Links to Oracle Documentation.......................................................................................................72

CRM Resources......................................................................................................73Conversion Short Description..................................................................................................................................74Data Category..........................................................................................................................................................74Interface Tables.......................................................................................................................................................74Concurrent Program / /APIs....................................................................................................................................74Parameters................................................................................................................................................................74Setups/Pre-requisites................................................................................................................................................75Process Flow Diagram.............................................................................................................................................76Technical Flow Diagram for the Loading data from staging tables to Oracle base tables:.....................................77Different scenarios of using the APIs......................................................................................................................80Field Mapping details..............................................................................................................................................82Validation Logic......................................................................................................................................................82Errors and Solutions................................................................................................................................................83Data Validation Techniques....................................................................................................................................84Tuning Methods.......................................................................................................................................................84Appendix A – Links to Oracle Documentation.......................................................................................................84Appendix A – Definitions........................................................................................................................................85

Counters Load.......................................................................................................87Conversion Short Description..................................................................................................................................88Data Category:.........................................................................................................................................................88Interface Tables.......................................................................................................................................................88Base Tables..............................................................................................................................................................88Error Tables.............................................................................................................................................................88Concurrent Program / /APIs....................................................................................................................................88


Parameters................................................................................................................................................................88APIs Involved..........................................................................................................................................................88Setups/Pre-requisites................................................................................................................................................89Process Flow Diagram.............................................................................................................................................90Field Mapping details..............................................................................................................................................91Validation Logic......................................................................................................................................................91Errors and Solutions................................................................................................................................................96

AR Receipts Open & Closed....................................................................................97Conversion Short Description..................................................................................................................................98Data Category:.........................................................................................................................................................98Interface Tables.......................................................................................................................................................98Base Tables..............................................................................................................................................................98Error Tables.............................................................................................................................................................98Concurrent Program / /APIs....................................................................................................................................98Parameters................................................................................................................................................................98APIs Involved..........................................................................................................................................................98Setups/Pre-requisites................................................................................................................................................99Process Flow Diagram...........................................................................................................................................100Field Mapping details............................................................................................................................................101Validation Logic....................................................................................................................................................101Errors and Solutions..............................................................................................................................................102

GL Balances Load.................................................................................................103Conversion Short Description................................................................................................................................104Data Category:.......................................................................................................................................................104Interface Tables.....................................................................................................................................................104Base Tables............................................................................................................................................................104Error Tables...........................................................................................................................................................104Concurrent Program / /APIs..................................................................................................................................104Parameters..............................................................................................................................................................104APIs Involved........................................................................................................................................................104Setups/Pre-requisites..............................................................................................................................................105Process Flow Diagram...........................................................................................................................................106Field Mapping details............................................................................................................................................107Validation Logic....................................................................................................................................................107Errors and Solutions..............................................................................................................................................107

GL Budget Load....................................................................................................109Conversion Short Description................................................................................................................................110Data Category:.......................................................................................................................................................110Interface Tables.....................................................................................................................................................110Base Tables............................................................................................................................................................110Error Tables...........................................................................................................................................................110Concurrent Program / /APIs..................................................................................................................................110Parameters..............................................................................................................................................................110APIs Involved........................................................................................................................................................110Setups/Pre-requisites..............................................................................................................................................111Process Flow Diagram...........................................................................................................................................112Field Mapping details............................................................................................................................................113Validation Logic....................................................................................................................................................113Appendix A – Definitions......................................................................................................................................114

AR Invoices Open & Closed...................................................................................115Conversion Short Description................................................................................................................................116Data Category:.......................................................................................................................................................116Interface Tables.....................................................................................................................................................116Base Tables............................................................................................................................................................116Error Tables...........................................................................................................................................................116Concurrent Program / /APIs..................................................................................................................................116


Parameters..............................................................................................................................................................116APIs Involved........................................................................................................................................................116Setups/Pre-requisites..............................................................................................................................................117Process Flow Diagram...........................................................................................................................................118Field Mapping details............................................................................................................................................119Validation Logic....................................................................................................................................................119

Purchase Orders..................................................................................................123Conversion Short Description................................................................................................................................124Data Category:.......................................................................................................................................................124Interface Tables.....................................................................................................................................................124Base Tables............................................................................................................................................................124Error Tables...........................................................................................................................................................124Concurrent Program / /APIs..................................................................................................................................124Parameters..............................................................................................................................................................124APIs Involved........................................................................................................................................................124Setups/Pre-requisites..............................................................................................................................................125Process Flow Diagram...........................................................................................................................................126Field Mapping details............................................................................................................................................127Validation Logic....................................................................................................................................................127

Bill of Material.....................................................................................................130Conversion Short Description................................................................................................................................131Data Category:.......................................................................................................................................................131Interface Tables.....................................................................................................................................................131Base Tables............................................................................................................................................................131Error Tables...........................................................................................................................................................131Concurrent Program / /APIs..................................................................................................................................131Parameters..............................................................................................................................................................131APIs Involved........................................................................................................................................................131Setups/Pre-requisites..............................................................................................................................................132Process Flow Diagram...........................................................................................................................................133Field Mapping details............................................................................................................................................134Validation Logic....................................................................................................................................................134Errors and Solutions:.............................................................................................................................................135

PO Receipts (Open)..............................................................................................136Conversion Short Description................................................................................................................................137Data Category:.......................................................................................................................................................137Interface Tables.....................................................................................................................................................137Base Tables............................................................................................................................................................137Error Tables...........................................................................................................................................................137Concurrent Program / /APIs..................................................................................................................................137Parameters..............................................................................................................................................................137APIs Involved........................................................................................................................................................137Setups/Pre-requisites..............................................................................................................................................138Process Flow Diagram...........................................................................................................................................139Field Mapping details............................................................................................................................................140Validation Logic....................................................................................................................................................140Errors and Solutions:.............................................................................................................................................141

Fixed Asset (Operating)........................................................................................142Conversion Short Description................................................................................................................................143Data Category:.......................................................................................................................................................143Interface Tables.....................................................................................................................................................143Base Tables............................................................................................................................................................143Error Tables...........................................................................................................................................................143Concurrent Program / /APIs..................................................................................................................................143Parameters..............................................................................................................................................................143APIs Involved........................................................................................................................................................143


Setups/Pre-requisites..............................................................................................................................................144Process Flow Diagram...........................................................................................................................................145Field Mapping details............................................................................................................................................147Validation Logic....................................................................................................................................................147Errors and Solutions:.............................................................................................................................................149Appendix................................................................................................................................................................149

Sales Orders (Open & Closed)...............................................................................150Conversion Short Description................................................................................................................................151Data Category:.......................................................................................................................................................151Interface Tables.....................................................................................................................................................151Base Tables............................................................................................................................................................151Error Tables...........................................................................................................................................................151Concurrent Program / /APIs..................................................................................................................................151Parameters..............................................................................................................................................................151APIs Involved........................................................................................................................................................151Setups/Pre-requisites..............................................................................................................................................152Process Flow Diagram...........................................................................................................................................152Field Mapping details............................................................................................................................................156Validation Logic....................................................................................................................................................156Errors and Solutions:.............................................................................................................................................161Appendix................................................................................................................................................................163

Service Contracts.................................................................................................164Conversion Short Description................................................................................................................................165Data Category:.......................................................................................................................................................165Interface Tables.....................................................................................................................................................165Base Tables............................................................................................................................................................165Concurrent Program / /APIs..................................................................................................................................165Parameters..............................................................................................................................................................165Setups/Pre-requisites..............................................................................................................................................165Process Flow Diagram...........................................................................................................................................166Field Mapping details............................................................................................................................................168Validation Logic....................................................................................................................................................168Errors and Solutions..............................................................................................................................................172Appendix A – Links to Oracle Documentation.....................................................................................................173Appendix B – Definitions......................................................................................................................................173

SALES CONTRACTS...............................................................................................174Conversion Short Description................................................................................................................................175Data Category:.......................................................................................................................................................175Interface Tables.....................................................................................................................................................175Base Tables............................................................................................................................................................175Concurrent Program / /APIs..................................................................................................................................175Parameters..............................................................................................................................................................175Setups/Pre-requisites..............................................................................................................................................175Process Flow Diagram...........................................................................................................................................176Field Mapping details............................................................................................................................................178Validation Logic....................................................................................................................................................178Errors and Solutions..............................................................................................................................................179Data Validation Techniques..................................................................................................................................180Tuning Methods.....................................................................................................................................................180Appendix A – Links to Oracle Documentation.....................................................................................................181

Appendix B – Definitions.............................................................................................................181


Document Control

Change Record

Date Author Version Change Reference

1-Nov-2004 Rohit Jain 0.0

15-Nov-2004 Amitava Samaddar 0.0

15-Dec-2004 Dushmanta Mohanty 0.0

1-Jan-2005 Naveen Koka 1.0 Initial Review

28-Feb-2005 Dushmanta Mohanty 2.0 Added the AR Invoice Load

01-Mar-2005 Dushmanta Mohanty 2.1 Added the Purchase Order Load,Bill of Material LoadPO receipts - Open

02-Mar-2005 Dushmanta Mohanty 2.2 Fixed Asset (Operating)

15-Mar-2005 Dushmanta Mohanty 2.3 Sales Orders (Open & Closed)

05-Apr-2005 Dushmanta Mohanty 2.4 Service Contracts

18-Apr-2005 Savitha Rao 2.5 Modified Sales Orders and Counters


IntroductionThis document can be used as a Hand Book for any project that involves Data Conversions into Oracle.This document has been prepared while developing and executing the Data Conversions for Ricoh, USA. The project involved data conversions and development of numerous customizations in the form of Workflows, Reports, Interfaces and Extensions.

This Hand Book will be helpful to all Project Managers, Team Leads and Developers as it covers all aspects of the Conversions. This document covers details on the following areas of a Conversion:

1. Conversion Short description2. Interface Tables 3. Base Tables4. Error Tables5. Oracle Concurrent Program6. Parameters to the program7. APIs involved 8. Parameters to each API9. Pre Requisite Setup Steps10. Errors and issues encountered while testing11. Estimates for development of program

The authors and their contact information is listed below:

1. Rohit Jain2. Savitha Rao3. Amitava Samaddar4. Dushmanta Mohanty

Reviewer:1. Naveen Koka


Item Master


Conversion Short Description

Item Master Conversion involved import of items from any legacy system to the Oracle system.Once the Organization structure has been defined in Oracle, the Items will need to be defined in the

a) Item Master Organizationb) Other Child Organizations

Oracle has a vanilla program for this import. The name of the program is ‘Import Items’.

Using the Item Import Program you can perform the following operations : 1. Import the Items into Oracle Inventory.2. Create new items in your item master organization or assign existing items to additional organizations.3. You can specify values for all the item attributes, or you can specify just a few attributes andlet the remainder default or remain null. 4. The Item Interface also lets you import revision details, including past and future revisions and effectivity dates. 5. You can also use the Item Interface to import item material cost, material overhead, andrevision details.

The Item Interface reads data from two tables for importing items and item details. You use the MTL_SYSTEMS_ITEM_INTERFACE table for your new item numbers and all itemattributes. This is the main item interface table, and may be the only table you choose touse. If you are importing revision details for your new items, you can use the MTL_ITEM_REVISIONS_INTERFACE table. This table is used only for revision information, and isnot required. A third table, MTL_INTERFACE_ERRORS, is used for error tracking of all items that the Item Interface fails.Before you use the Item Interface, you must write and run a custom program that extractsitem information from your source system and inserts it into the MTL_SYSTEM_ITEM_INTERFACE table, and (if revision detail is included) the MTL_ITEMS_REVISIONS_INTERFACE table. After you load the items into these interface tables, you run the ItemInterface to import the data. The Item Interface assigns defaults, validates data you include,and then imports the new items.

You must import items into a master organization before you import items into additional organizations. You can accomplish this by specifying only your 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.

Data Category: Master

Interface Tables MTL_SYSTEM_ITEMS_INTERFACEMTL_ITEM_CATEGORIES_INTERFACEMTL_REVISIONS_INTERFACE (optional)

Concurrent Program / / / /APIs

Item Import Program(INCOIN)

Parameters When you run the Item Interface, you are prompted for report parameters. These are runtimeoptions for the Item Interface:All OrganizationsYes Run the interface for all organization codes in the item interface table.No Run the interface only for the organization you are currently in. Iteminterface rows for organizations other than your current organizationare ignored.


Validate ItemsYes Validate all items and their data residing in the interface table that havenot yet been validated. If items are not validated, they will not beprocessed into Oracle Inventory.No Do not validate items in the interface table. Note that items that havenot been validated will not be processed into Oracle Inventory. Youwould use this option if you had previously run the item interface andresponded Yes for Validate Items and No for Process Items, and nowwant to process your items.

Process ItemsYes All qualifying items in the interface table are inserted into OracleInventory.No Do not insert items into Oracle Inventory. Use this option, along withYes for Delete Processed Items, to remove successfully processedrows from the interface table without performing any other processing.You can also use this option, along with Yes for Validate Items, if youwant to validate items without any processing.

Delete Processed RowsYes Delete successfully processed items from the item interface tables.No Leave all rows in the item interface tables.

Process SetEnter a number for the set id for the set of rows you want to process. The program picks upthe rows marked with that id in the SET_PROCESS_ID column. If you leave this fieldblank, all rows are picked up for processing regardless of the SET_PROCESS_ID columnvalue.


Setups/Pre-requisites

SR. No.

Setup Name Setup Values Notes

1 Internal and standard concurrent managers

up and running system admin resp/concurrent/manager/administer

2 Check that the standard indexes exist on the mtl_system_items

SELECT TABLE_NAME,INDEX_NAME FROM ALL_INDEXESWHERE TABLE_NAME IN (MTL_SYSTEM_ITEMS_B,MTL_SYSTEM_ITEMS_INTERFACE)ORDER BY TABLE_NAME,INDEX_NAME


Process Flow Diagram

Database ERD

Interface Tables:

Base Tables:

1. MTL_SYSTEM_ITEMS_B2. MTL_ITEM_CATEGORIES3. MTL_ITEM_REVISIONS_B


Different ways of using the program / API

Scenario 1: Basic item import with minimum columns populated : Step1 : Minimum columns to be populated in MTL_SYSTEM_ITEMS_INTERFACE table are :

1. Process_flag => 1 2. Set_process_id => 13. Transaction_Type => ‘CREATE’4. Organization_Id => Valid Organization_id from MTL_PARAMETERS5. Segment1..n => Items Segments6. Description => Description of the Item

Scenario 2: Import items using item templates : Step1 : Minimum columns to be populated in MTL_SYSTEM_ITEMS_INTERFACE table are :

1. Process_flag => 1 2. Set_process_id => 13. Transaction_Type => ‘CREATE’4. Organization_Id => Valid Organization_id from MTL_PARAMETERS5. Segment1..n => Items Segments6. Description => Description of the Item 7. Template_id => Valid template from MTL_ITEM_TEMPLATES

Templates – are a collection of values assigned to Item Attributes (i.e. columns in the Item Master tables). Multiple Templates can be defined

o for different Item Types o for different Organizationso for different combinations of Item Type and Organization etc..

If you populate a value for an attribute in MTL_SYSTEM_ITEMS_INTERFACE table, and the same attribute has a value assigned in the Template – that has been assigned to the Item, the former will take precedence.

Scenario 3: Import items and material cost associated to it : Step1 : Minimum columns to be populated in MTL_SYSTEM_ITEMS_INTERFACE table are :

1. Process_flag => 1 2. Set_process_id => 13. Transaction_Type => ‘CREATE’4. Organization_Id => Valid Organization_id from MTL_PARAMETERS table5. Segment1..n => Items Segments6. Description => Description of the Item 7. Template_id => Valid template from MTL_ITEM_TEMPLATES8. Material_cost => Cost 9. Material_Sub_Elem => Valid Resource_Code from BOM_RESOURCES table

Scenario 4: Import item and revisions associated to it

Step1 : Minimum columns to be populated in MTL_ ITEM_REVISIONS_INTERFACE table are : 1. Item_number => Concatenation of Item Segments 2. Revision => New Revision3. Revision_label => New Revision Label4. Implementation_date => Date5. Effectivity_date => Date6. Process_flag => 17. Organization_id => Organization_id for the Item8. Set_process_id => Same as in MSII Table9. Transaction_type => ‘CREATE’

Scenario 5: Import item categories along with items


Step1 : Minimum columns to be populated in MTL_ ITEM_CATEGORIES_INTERFACE table are : 1. Item_number => Concatenation of Item Segments 2. Category_set_Id => Category_set_id from mtl_category_sets_v3. Category_Id => Category_Id from Mtl_Categories_V Where Category_set_id = <param2>4. Process_flag => 15. Organization_id => Organization_id for the Item6. Set_process_id => Same as in MSII Table7. Transaction_type => ‘CREATE’

Scenario 6: Updating existing item categories

Categories can be updated only by performing a combination of DELETE and then CREATE

Step1 : Minimum columns to be populated in MTL_ ITEM_CATEGORIES_INTERFACE table for DELETE : 1. Item_number => Concatenation of Item Segments 2. Category_set_Id => => Category_set_id from mtl_category_sets_v3. Category_Id => Category_Id from Mtl_Categories_V Where Category_set_id = <param2>4. Process_flag => 15. Organization_id => Organization_id for the Item6. Set_process_id => Same as in MSII Table7. Transaction_type => ‘DELETE’

Step2 : Minimum columns to be populated in MTL_ ITEM_CATEGORIES_INTERFACE table for CREATE : 8. Item_number => Concatenation of Item Segments 9. Category_set_Id => => Category_set_id from mtl_category_sets_v10. Category_Id => Category_Id from Mtl_Categories_V Where Category_set_id = <param2>11. Process_flag => 112. Organization_id => Organization_id for the Item13. Set_process_id => Same as in MSII Table14. Transaction_type => ‘CREATE’

Scenario 7: Import item catalogs along with itemsStep1 : Minimum columns to be populated in MTL_SYSTEM_ITEMS_INTERFACE table are :

1. Process_flag => 1 2. Set_process_id => 13. Transaction_Type => ‘CREATE’4. Organization_Id => Valid Organization_id from MTL_PARAMETERS5. Segment1..n => Items Segments6. Description => Description of the Item 7. Item_Catalog_Group_Id => Item_Catalog_Group_Id from MTL_ITEM_CATALOG_GROUPS_B

Scenario 8: Update existing items


Field Mapping details<insert the interface table – with field descriptions, and mandatory/non mandatory, and foreign key relationships if possible …. Insert as an object – what ever excel files you may have>

Validation Logic<insert any specific validations to be done before calling import program or APIs.Do not mention mandatory fields – that you have already specified in the field mapping details>


Errors and Solutions

Potential Errors Possible Reasons Solution Strategy

[Describe instances where errors may occur…keep high level. This should include the extent to which the organization is responsible for error monitoring in correction versus users responsibilities. Full details on how operations will be notified are not required; however, state the business expectations for level of service provided. Include details of validation routines]]

State the possible reasons State the possible solutions

BOM_PARSE_ITEM_ERROR*** BAD RETURN CODE b *** 1)Please see that setup > Receiving >

(Purchasing and Receiving) setups are complete.

2)Please auto extend all MTL Tables and MTL Indexes.

3)Please auto extend Table Space.

4)Re-Link all Inventory libraries. Relink INCOIN.o.

5)Compile all Inventory Key Flex Fields.

6)Compile all invalid objects.

7)Please check all INV profile options are set.

8)Now truncate the interface tables and populate it with few records and run import. The error should go.

1.


Data Validation Techniques <Forms, reports and queries that can be run to compare data between legacy and Oracle >

Conversion Tool Audit Log: Oracle Application Standard Report(s)

Tuning Methods<Indexes, methods that can be used to improve performance>

Appendix A – Links to Oracle Documentation

[Enter conversion enhancements, updates to the design, etc. Please reference the changes in the original design and include the author and date in the appendix as well as in the Change Control section.]

Refer to the Oracle Manufacturing APIs and Interfaces Manual and Oracle Inventory User’s Guide on Metalink for the Inventory Transactions APIs and functionality.

User Guide - http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284553Inventory APIs Vol. 1 - http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284259Inventory APIs Vol. 2 - http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284260

Refer to the ERD Diagram on the Oracle eTRM website for relationships between the Inventory base tables (Inventory ERD and Item ERD).

Oracle Inventory ERD - http://etrm.oracle.com/pls/etrmlatest/etrm_fndnav.show_file?n_file_id=145&c_mode=INLINEOracle Item ERD - http://etrm.oracle.com/pls/etrmlatest/etrm_fndnav.show_file?n_file_id=146&c_mode=INLINE

http://etrm.oracle.com/pls/etrmlatest/etrm_fndnav.show_file?n_file_id=146&c_mode=INLINE




http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284260




Appendix A – Definitions

Accounting Period – The fiscal period a company uses to report financial results, such as a calendar month or fiscal period.

Application Programming Interface (API) – The APIs provide an extensive error handling and error-reporting mechanism whereby all errors encountered in the different phases of API execution are reported and put on the message stack. The calling program has the option of looking up all the error messages or the first error message on the stack. If there is only one error in the message stack, the error comes out as one of the output parameters of the API routine and the user does not need to fetch the message from the stack.

API Error StatusesThe return status (x_return_status) of the API informs the caller about the result of the operation (or operations) performed by the API. The different possible values for an API return status are listed below:

Success (FND_API.G_RET_STS_SUCCESS) – A success return status means that the API was able to perform all the operations requested by its caller. A success return status may be accompanied by messages in the API message list that will be informative.

Error (FND_API.G_RET_STS_ERROR) – An error return status means that the API failed to perform some or all of the operations requested by its caller. An error return status is usually accompanied by messages describing the error (or errors) and how to fix it. In most cases, you should be able to take corrective actions to fix regular, expected errors such as missing attributes or invalid date ranges.

Unexpected error (FND_API.G_RET_STS_UNEXP_ERROR) – An unexpected error status means that the API has encountered an error condition it did not expect or could not handle. In this case, the API is unable to continue with its regular processing. Examples of such error are irrecoverable data inconsistency errors, memory errors, and programming errors (like attempting a division by zero). In most cases, the end user will not be able to correct unexpected errors. It is usually system administrators or application developers who can fix these errors.

Bill of Material (BOM) – A list of component items associated with a parent item and information about how each item relates to the parent item. Oracle Manufacturing supports standard, model, option class, and planning bills. The item information on a bill depends on the item type and bill type. The most common type of bill is a standard bill of material. A standard bill of material lists the components associated with a product or subassembly. It specifies the required quantity for each component plus other information to control work in process, material planning, and other Oracle Manufacturing functions. Also known as product structures.

BOM Item Type – An item classification that determines the items you can use as components in a bill of material. BOM Item types include standard, model, option class, and planning items.

Category – Code used to group items with similar characteristics, such as plastics, metals, or glass items.

Category Sets – A feature in Inventory where users may define their own group of categories. Typical category sets include purchasing, materials, costing, and planning.

Concurrent Manager – Components of your applications concurrent processing facility that monitor and run time–consuming tasks for you without tying up your terminal. Whenever you submit a request, such as running a report, a concurrent manager does the work for you, letting you perform many tasks simultaneously.

Concurrent Process – A task in the process of completing. Each time you submit a task, you create a new concurrent process. A concurrent process runs simultaneously with other concurrent processes (and other activities on your computer) to help you complete multiple tasks at once with no interruptions to your terminal.

Configuration – A product a customer orders by choosing a base model and a list of options. It can be shipped as individual pieces as a set (kit) or as an assembly (configuration item).

Inventory Controls – Parameter settings that control how Inventory functions.


Inventory Item – Items you stock in inventory. You control inventory for inventory items by quantity and value. Typically, the inventory item remains an asset until you consume it. You recognize the cost of an inventory item as an expense when you consume it or sell it. You generally value the inventory for an item by multiplying the item standard cost by the quantity on hand.

Item Attributes – Specific characteristics of an item, such as order cost, item status, revision control, COGS account, etc.

Item Status – Code used to control the transaction activity of an item.

Locator – Physical area within a subinventory where you store material, such as a row, aisle, bin, or shelf.

Logical Organization – A business unit that tracks items for accounting purposes but does not physically exist.

On-hand Quantity – The physical quantity of an item existing in inventory.

Organization – A business unit such as a plant, warehouse, division, department, and so on. Order Management refers to organizations as warehouses on all Order Management windows and reports.

Physical Inventory – A periodic reconciliation of item counts with system on–hand quantities.

Revision – A particular version of an item, bill of material, or routing.

Serial Number – A number assigned to each unit of an item and used to track the item.

Serialized Unit – The unique combination of a serial number and an inventory item.

Set of Books – A financial reporting entity that partitions General Ledger information and uses a particular chart of accounts, functional currency, and accounting calendar. This concept is the same whether or not the Multi–organization support feature is implemented.

Standard Costing – A costing method where a predetermined standard cost is used for charging material, resource, overhead, period close, job close, and cost update transactions and valuing inventory. Any deviation in actual costs from the predetermined standard is recorded as a variance.

Subinventory – Subdivision of an organization, representing either a physical area or a logical grouping of items, such as a storeroom or receiving dock.

Transaction Interface – An open interface table through which you can import transactions.

Transaction Manager – A concurrent program that controls your manufacturing transactions.

Transaction Worker – An independent concurrent process launched by a transaction manager to validate and process your manufacturing transactions.

Unit of Measure (UOM) – The unit that the quantity of an item is expressed.

Unit of Measure Class – A group of units of measure and their corresponding base unit of measure. The standard unit classes are Length, Weight, Volume, Area, Time, and Pack.

Unit of Measure Conversion – Numerical factors that enable you to perform transactions in units other than the primary unit of the item being transacted.


INSTALLBASE



Oracle Install Base is an item instance life cycle tracking application that facilitatesenterprise-wide life cycle item management and tracking capabilities. Oracle InstallBase tracks an item from the time that it is received in inventory, in work in process,in projects, at customer sites, and throughout the return and repair process. Itrecords a history of changes to tracked items and supports the creation andmaintenance of Oracle Install Base configurations.

Oracle Install Base is a centralized repository of information for an item instance and its tracking details including location, status, ownership, party relationships, account relationships, contact relationships, configuration data, and the change history of customer products or corporate assets.

As you define items in Oracle Inventory, you can designate your items as serviceable products. When you designate an item as a serviceable product, the installed base will track them. Each time you sell a serviceable product in Oracle Order Entry, the installed base interface automatically creates a customer product record in the Oracle Service installed base.

A customer product is an instance of a particular product you sold to an end customer or distributor. The customer product reference number uniquely identifies any customer product, with or without a serial number, in the installed base.Quantity, serial number (the serviceable product must be under serial control in Oracle Inventory and assigned a serial number at the time of shipment), location code (bill-to, ship-to, and installation location), contacts, order information, and revision history further distinguishes thecustomer product.

Oracle Install Base provides the Open Interface feature to mass load item instances. This is useful during initial conversion and for synchronizing multiple environments. It can be used to create new instances or to update existing item instances. It can also be used to create or update relationships between item instances including replacing instances in Component-Of configurations.


Interface Tables CSI_INSTANCE_INTERFACECSI_I_PARTY_INTERFACECSI_IEA_VALUE_INTERFACECSI_II_RELATION_INTERFACE

Base Tables CSI_ITEM_INSTANCESCSI_I_PARTIESCSI_IEA_VALUESCSI_II_RELATIONSHIPSCSI_I_PRICING_ATTRIBS

Concurrent Program / /APIs

Install Base Open Interface (CSICONB)

Parameters When you run the Install Base Interface Program, you are prompted for report parameters. These are runtime options for the Interface program:


From Transaction Date Starting Transaction Date to Load the InstancesTo Transaction DateEnd Transaction Date to Load the Instances.Source System NameSource System Name is used as selection criteria for the rows to be processed from Interface table. It should exist in setup table csi_interface_sources.Batch Name : Batch Name of the records to be Process. If NULL is passed all the records will be selected for processing.Resolve Oracle Ids :Yes : Resolves the ids from different tables from the descriptive values provided in the interface tables and also validates the existence of all the referenced IdsNo : Doesn’t Resolve the Ids.Purge Processed RecordsYes Delete successfully processed rows.No Leave all interface tables for successfully processed rows.Error Reprocess OptionsAllSelected


SR. No.


1. Source system details setup

Populating at least one record into the csi_interface_sources table.



Database ERD<show the Various links between interface and base tables >

Interface Tables:


Base Tables:


Ways of using the program / API

Scenario 1 : Creating Instance Minimum columns to be populated in CSI_INSTANCE_INTERFACE table are :

a. INST_INTERFACE_ID => Sequenceb. SOURCE_SYSTEM_NAME => Source System Name from CSI_INTERFACE_SOURCESc. TRANSACTION_IDENTIFIER => Transaction_identifier in csi_instance_interface table represents

a transaction. This column along with source_system_name forms a unique key. Open interface records will be processed as units for commit or rollback. All the records with the same transaction_identifier will be considered to correspond to a single transaction in the source system.

d. SOURCE_TRANSACTION_DATE => Transaction Date that this Instance will be processed

with the Open Interfacee. PROCESS_STATUS => Process Status for Instance. Value Values are 'R' - Ready, 'E' -

Error, 'P' - Processedf. INVENTORY_ITEM_ID => Inventory Item Id for the instance you are interfacingg. INV_VLD_ORGANIZATION_ID => Validation Organization ID that is used for Validating the

Inventory Attributesh. UNIT_OF_MEASURE_CODE => Unit of Measure Code that will be used for this Instance (i.e.

'EA')Minimum columns to be populated in CSI_I_PARTY_INTERFACE table are :

a. IP_INTERFACE_ID => Instance Party Interface IDb. INST_INTERFACE_ID => Instance Interface ID that will be used to link the rows in this

table to its parent in CSI_INSTANCE_INTERFACEc. PARTY_ID => Party Idd. PARTY_NUMBER => Party Numbere. PARTY_SOURCE_TABLE => Party Source Table for ex ‘HZ_PARTIES’f. PARTY_RELATIONSHIP_TYPE_CODE => IPA_RELATION_TYPE_CODE from

CSI_IPA_RELATION_TYPES table for ex ‘OWNER’ . Owner Relationship is mandatory for Instance Creation .

g. CONTACT_FLAG => Primary Contact Flag ( ‘Y’ if party is a Contact else ‘N’)h. PARTY_ACCOUNT1_NUMBER => Populate HZ_CUST_ACCOUNTS.Account_number by making

Party_Id joini. PARTY_ACCOUNT1_ID => Populate HZ_CUST_ACCOUNTS.Cust_Account_Id by making

Party_Id joinj. ACCT1_RELATIONSHIP_TYPE_CODE => ‘OWNER’

Scenario 2 : Creating Instance and Relationships :Along with CSI_INSTANCE_INTERFACE and CSI_I_PARTY_INTERFAC table we need to populate

CSI_II_RELATION_INTERFACE table.Minimum columns to be populated in CSI_II_RELATION_INTERFACE table are :1. REL_INTERFACE_ID => Relationship Interface ID (Sequence)2. SUBJECT_INTERFACE_ID => Parent Instance_Interface_Id from CSI_INSTANCE_INTERFACE table

3. OBJECT_INTERFACE_ID => Child Insntance_Interface_Id from CSI_INSTANCE_INTERFACE table

4. RELATIONSHIP_TYPE_CODE =>Populate CSI_II_RELATION_TYPES.RELATIONSHIP_TYPE_CODE for example ‘COMPONENT-OF’

Note: The Transaction_Identifier in CSI_INSTANCE_INTERFACE table for both the Parent and Child Instances should be same.


Scenario 3 : Creating Instance with Extended Attributes:Along with CSI_INSTANCE_INTERFACE and CSI_I_PARTY_INTERFAC table we need to populate

CSI_IEA_VALUE_INTERFACE table.Minimum columns to be populated in CSI_IEA_VALUE_INTERFACE table are :1. IEAV_INTERFACE_ID => Extended Attribute Interface ID (Sequence)2. INST_INTERFACE_ID => Instance Interface ID that will be used to link the rows in this table to its

parent in CSI_INSTANCE_INTERFACE3. ATTRIBUTE_ID => ATTRIBUTE_ID FROM csi_i_extended_attribs 4. ATTRIBUTE_CODE => ATTRIBUTE_CODE FROM csi_i_extended_attribs5. ATTRIBUTE_VALUE => Value for Attribute



CSI_INSTANCE_INTERFACE

CSI_I_PARTY_INTERFACE

CSI_II_RELATION_INTERFACE

CSI_IEA_VALUE_INTERFACE

Validation Logic



Install Base extract program populatedthe CSI_II_REALTION_INTERFACE table, however no recordswere ultimately processed for relationships.

Transaction Identifier field in CSi_INSTANCE_INTERFACE table not populated

It is required to populate the sametransaction_identifier for all the related instance . For example ifsubject_interface_id=5 and object_interface_id = 4 inCSI_II_RELATION_INTERFACE table then the rows havinginstance_interface_id = 4 and 5 in CSI_INSTANCE_INTERFACEtable should have same transaction_identifier







User Guide - Oracle Install Base User Guide Release 11.5.9, Part B10683-01<131739.1>

http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=131739.1&p_showHeader=1&p_showHelp=0#IB


INVENTORY TRANSACTIONS



Oracle Inventory provides an open interface for you to easily load transactions from externalapplications and feeder systems. These transactions could include sales order shipmenttransactions from an order entry system other than Oracle Order Entry, or they could besimple material issues, receipts, or transfers loaded from data collection devices. Thefollowing transaction types are supported by this interface:Inventory issues and receipts (including user-defined transaction types)Subinventory transfersDirect inter-organization transfersIntransit shipmentsWIP component issues and returnsWIP assembly completions and returnsSales order shipmentsInventory average cost updates

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 lotor serial control, you must also insert rows into MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE respectively. If you insertWIP assembly/completion transactions that complete or return job assemblies, you must alsoinsert rows into the CST_COMP_SNAP_ INTERFACE table if the organization referenceduses 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 thefirst processing mode, you populate the interface table only. Then the Transaction Managerpolls the interface table asynchronously looking for transactions to process, groups thetransaction 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 theworker can recognize which subset of transactions to process.The Transaction Worker calls the Transaction Validator which validates the row, updates theerror code and explanation if a validation or processing error occurs, and derives or defaultsany additional columns.Next, the Transaction Processor records the transaction details in the transaction historytable along with relevant current cost information. All material movement transactionsupdate inventory perpetual balances for the issue, receipt, or transfer locations.Once the transaction has been successfully processed, the corresponding row is deleted fromthe interface table. Finally, the transaction is costed by the transaction cost processor whichruns periodically, picking up all transactions from the history table that have not yet beenmarked as ‘costed’.

The rows in mtl_transactions_interface are processed in 5 phases.

1. Derives dependant columns, eg:acct_period_id, primary_quantity etc.. 2. Detailed validation performed on the records 3. On hand qty check for negative qty's etc.. 4. Reservations Relieved if demand was created in order entry 5. Rows are moved to mtl_material_transactions_temp where the transaction processor is called to process these rows and update the inventory levels etc..

Data Category: Transaction

Interface Tables MTL_TRANSACTIONS_INTERFACEMTL_TRANSACTION_LOTS_INTERFACEMTL_SERIAL_NUMBERS_INTERFACE

Base Tables MTL_MATERIAL_TRANSACTIONS


MTL_SERIAL_NUMBERSMTL_ONHAND_QUANTITIES_DETAILMTL_UNIT_TRANSACTIONSMTL_LOT_NUMBERS


Process transaction interface ( INCTCM)

Parameters None


SR. No.


1. Setting the Parallel degree for Inventory Transaction Manager

The number of target processes will limit the number of workers available to the transaction manager, workers can be run in parallel, therefore to significantly reduce the time taken to process your inventory transactions,then increase the number of target processes to the maximum allowable within your system limits. For this request the assistance of your dba, system memory and central processor performance will be the limiting factors for this target parameter.

2. Transaction Worker Rows

Navigate - > Inventory Resp : Setups->Transactions->Interface Managers->Worker Rows = 200



LegacySystem

OracleInterface Table

OracleTransaction

Manager(INCTCM)

Oracle BaseTables

LegacyExtractionProcess

Errors

OracleTransaction

Worker(INCTCW)


Interface Tables:


Base Tables:


Ways of using the program / API

Scenario 1 : Transactions for Non Serial and Non Lot Controlled Items :

Minimum fields to be populated in MTL_TRANSACTIONS_INTERFACE table are :

SOURCE_CODE => This column is required for Sales Order transactions to identify the source Order Entry system. For other transaction types, you can enter any useful value for tracking purposes.The values entered are transferred directly to the transaction history table.

SOURCE_LINE_ID => You can use this column as an external system reference. The values entered are transferred directly to the transaction history table.

SOURCE_HEADER_ID => You can use this column as an external system reference. The values entered are transferred directly to the transaction history table.

PROCESS_FLAG => This column controls whether rows in the interface table are processed. You should insert a row that you intend to be processed with a value of 1 (Yes). The valid values are:

1 – Yes 2 – No 3 - Error

TRANSACTION_MODE => This column determines how the interfaced transactions will be processed. The valid options are: 2 – Concurrent 3 – Background Interface transactions marked for Background processing will be picked up by the transaction manager polling process and assigned to a transaction worker. These transactions will not be processed unless the transaction manager is running.You use Concurrent transaction mode if you want to launch a dedicated transaction worker to explicitly process a set of transactions. The Transaction Manager does not process transactions marked for concurrent processing.

LOCK_FLAG => The Transaction Manager uses this column to manage the worker assignment process. You should need to update this column only if a transaction has failed due to an exceptional failure such as the system going down in the middle of transaction worker processing. In this case, you will need to reset the LOCK_FLAG to 2 so your failed transactions can be reprocessed.

TRANSACTION_HEADER_ID => This column groups transactions for assignment to specific transaction workers. Depending on the value of TRANSACTION_MODE, this column is either required (concurrent mode) or derived by the transaction manager (background mode). This column maps to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SET_ID in the transaction history tables.

TRANSACTION_INTERFACE_ID => This column is required for transactions of items under lot or serial control. The value in the column in this table is used to identify the child rows in the lot or serial interface tables MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE.If the transacted item is under lot control, this column maps to MTL_TRANSACTION_LOTS_INTERFACE.TRANSACTION_INTERFACE_ID. If the transacted item is under serial control and not lotcontrol, this column maps to MTL_SERIAL_NUMBERS_INTERFACE.TRANSACTION_INTERFACE_ID.

INVENTORY_ITEM_ID => Active Item from MTL_SYSTEM_ITEMS_B

ORGANIZATION_ID => Active Organization from MTL_PARAMTERS

TRANSACTION_QUANTITY => Enter the transaction quantity in the transaction unit of measure. The quantity should be positive for receipts into inventory, and negative for both issues out of inventory and transfers. Enter a quantity of 0 for Average Cost Update transactions.

TRANSACTION_UOM => You can enter the TRANSACTION_QUANTITY in any unit of measure that has conversion rates defined to the item’s primary unit of measure. Use this column to specify the transacted unit of measure even if it is the same as the primary unit of measure.

TRANSACTION_DATE => Transaction Date


DISTRIBUTION_ACCOUNT_ID => Use this column (or the flexfield segment columns) to specify the account to charge for the cost of the Inventory transaction. It is required for user-defined transactions, and derived by the Transaction Worker based on the transaction source type and source for AccountIssue/Receipt and Account Alias Issue/Receipt transactions.

TRANSACTION_SOURCE_ID => TRANSACTION_SOURCE_ID or the corresponding flexfield segment columns (DSP_SEGMENT1 to DSP_SEGMENT30) are required for all transaction source types other than those that are user-defined. You should enter the foreign key ID that points to the context table identified by the transaction source type.

TRANSACTION_SOURCE_NAME => This column is required for user-defined transaction source types. Enter the value of the source name, such as an order number, to be displayed on all transaction reports and inquiries.

TRANSACTION_TYPE_ID => Enter the type of transaction you are executing.

Scenario 2 : Transactions for Serial Controlled and Non Lot Controlled Items

Minimum fields to be populated in MTL_SERIAL_NUMBERS_INTERFACE table are :

TRANSACTION_INTERFACE_ ID => Use this column to associate serial number transaction detail rows with their parent rows. If the item is serial controlled and not lot controlled , this should point to MTL_TRANSACTIONS_INTERFACE.TRANSACTION_INTERFACE_ID

FM_SERIAL_NUMBER => Enter the starting serial number in the range. If you enter only the ‘from’ serial number, the Transaction Processor assumes that only one serial number is being transacted.

TO_SERIAL_NUMBER => You can enter a ‘to’ serial number to specify a range. The transaction processor will attempt to transact all serial numbers within the range of the right-most numeric digits.

Scenario 3 : Transactions for Not Serial Controlled and Lot Controlled Items

Minimum fields to be populated in MTL_TRANSACTION_LOTS_INTERFACE table are :

TRANSACTION_INTERFACE_ID => Use this column to associate lot transaction detail rows with the parent transaction row in MTL_TRANSACTIONS_INTERFACE.

LOT_NUMBER => Enter the lot number that is being transacted.

TRANSACTION_QUANTITY => Quantity

Scenario 4 : Transactions for Serial and Lot Controlled Items

Minimum fields to be populated in MTL_SERIAL_NUMBERS_INTERFACE table are :

TRANSACTION_INTERFACE_ ID => Use this column to associate serial number transaction detail rows with their parent rows. If the item is serial lot controlled both , this should point to MTL_TRANSACTION_LOTS_INTERFACE.SERIAL_TRANSACTION_TEMP_ID.

FM_SERIAL_NUMBER => Enter the starting serial number in the range. If you enter only the ‘from’ serial number, the Transaction Processor assumes that only one serial number is being transacted.


TO_SERIAL_NUMBER => You can enter a ‘to’ serial number to specify a range. The transaction processor will attempt to transact all serial numbers within the range of the right-most numeric digits.

Minimum fields to be populated in MTL_TRANSACTION_LOTS_INTERFACE table are :

TRANSACTION_INTERFACE_ID => Use this column to associate lot transaction detail rows with the parent transaction row in MTL_TRANSACTIONS_INTERFACE.

LOT_NUMBER => Enter the lot number that is being transacted.

TRANSACTION_QUANTITY => Quantity

SERIAL_TRANSACTION_TEMP_ID=> This column is required only for items under both lot and serial control. It is used to identify the child rows in MTL_SERIAL_NUMBERS_INTERFACE.


MTL_TRANSACTIONS_INTERFACE

MTL_TRANSACTION_LOTS_INTERFACE

MTL_SERIAL_NUMBERS_INTERFACE


Validation Logic



Inventory item ID is invalid or does not have the flags enabled correctly to be processed by material processor

Item exist in master Org but not in organization which is specified in Transaction

Assign the Item in Transaction Organization

Subinventory code is either not entered or not valid for the given organization

Subinventory Code not defined in Organization Define the Subinventory Code in Transaction Organization and validate it from MTL_SECONDARY_INVENTORIES table.

Total serial numbers does not match the transaction quantity

This is caused because Transaction_quantity is more than the number of serial_number lines in MTL_SERIAL_NUMBERS_INTERFACE table. For ex. if transaction_quantity is 5 there should be 5 serial number entries in MTL_SERIAL_NUMBERS_INTERFACE table.

Enter the same number of records in MTL_SERIAL_NUMBERS_INTERFACE as the Transaction quantity.

No open period found for date entered Accounting periods not open for Transaction Date Open the accounting periods in Inventory . Navigate : Inventory Resp : Accounting Close Cycle -> Inventory Accounting Periods





1. Set the Parallel degree for Inventory Manager. This will decide the number of Parallel transaction workers to run. To set this go to System Administrator and navigate Concurrent > Manager > Define; Query for 'Invnetory Manager'; Click 'Work Shifts’. Set the parameter ‘Processes’ which will determine the number of parallel workers. For optimal processing in the Inventory Transaction Interface, you need to set up your concurrent manager to best handle your transaction volumes while balancing your performance requirements and your system load restrictions. Oracle Inventory ships the Transaction Manager to be run in Inventory’s own concurrent manager named Inventory Manager. It is defaulted to run in the Standard work shift with Target Processes = 1 andSleep Time of 60 seconds. See: Transaction Managers, Oracle Inventory User’s Guide. With this configuration, the Material Transaction Manager and all Transaction Workers that are spawned must share the same processing queue. If you have the available resources, you can substantially reduce the time to process your interfaced transactions by increasing the target processes and reducing the concurrent manager sleep time using the Concurrent Managers window. This will allow Transaction Workers to run in parallel with the Transaction Manager and with each other. See: Defining Managers and their Work Shifts,


Refer to the Oracle Manufacturing APIs and Interfaces Manual and Oracle Inventory User’s Guide on Metalink for the Inventory Transactions APIs and functionality.

User Guide - http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284553Inventory APIs Vol. 1 - http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284259Inventory APIs Vol. 2 - http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284260

Refer to the ERD Diagram on the Oracle eTRM website for relationships between the Inventory base tables (Inventory ERD and Item ERD).

Oracle Inventory ERD - http://etrm.oracle.com/pls/etrmlatest/etrm_fndnav.show_file?n_file_id=145&c_mode=INLINEOracle Item ERD - http://etrm.oracle.com/pls/etrmlatest/etrm_fndnav.show_file?n_file_id=146&c_mode=INLINE









Customer Master



Customer Master conversions involve the loading of customers related legacy data into the Oracle System. Each prospect in the business is identified as a party. The parties can be one of four types:

1. Organization-for example, Oracle Corporation.2. Person-for example, Jane Doe.3. Group-for example, World Wide Web Consortium.4. Relationship-for example, Jane Doe at Oracle Corporation.

Primary entities involved in Customer Master Conversions are1. Party2. Organization Profiles / Person Profiles3. Location4. Customer Accounts5. Customer Account Sites6. Customer Site Uses7. Relationship Types8. Relationships between person and Organization9. Relationship between related customers10. Contact point details of the customers.11. Tax Exemption details.

The conversion process will begin by extracting data from multiple systems and loading to staging tables. Several PL/SQL scripts will be used to pre-validate the data fetched from staging tables, call and pass the data to the customer APIs provided by Oracle.

Once the data is passed to the customer APIs the data will either be accepted by Oracle or be errored out. If the API accepts the data then the data will be written to the base tables. Conversely, if the data is errored out the data will require a fix prior to rerunning the information or manually entering the data into the application. Also data will be populated into a custom Cross Reference Table to align the legacy cust_bll_to and cust_shp_to with the Oracle ids/numbers.


Interface Tables CNV_HZ_PARTIESCNV_HZ_ORG_PROFILES CNV_HZ_PERSON_PROFILESCNV_HZ_CUST_ACCOUNTSCNV_HZ_CUSTOMER_PROFILESCNV_HZ_CUST_PROFILE_AMTSCNV_HZ_LOCATIONSCNV_HZ_PARTY_SITESCNV_HZ_CUST_ACCT_SITESCNV_HZ_CUST_SITE_USESCNV_HZ_CONTACT_POINTSCNV_HZ_PARTY_REL_TYPESCNV_HZ_RELATIONSHIPSCNV_HZ_CUST_ACCT_RELATECNV_XREF_CUSTOMERSCNV_RA_TAX_EXEMPTIONSCNV_AP_CREDIT_CARD_ACCOUNTS

Base Tables HZ_PARTIESHZ_ORGANIZATION_PROFILESHZ_CUST_ACCOUNTS_ALLHZ_CUSTOMER_PROFILESHZ_CUST_PROFILE_AMTSHZ_LOCATIONSHZ_PARTY_SITES


HZ_CUST_ACCT_SITES_ALLHZ_CUST_SITE_USES_ALLHZ_CONTACT_POINTSHZ_ORG_CONTACTSHZ_PERSON_PROFILESHZ_CUST_ACCOUNT_ROLESHZ_ROLE_RESPONSIBILITYHZ_RELATIONSHIPSHZ_CUST_ACCT_RELATE_ALLHZ_ORIG_SYS_REFERENCESRA_TAX_EXEMPTIONSAP_BANK_ACCOUNTS_ALL

Error Tables CNV_CONVERSION_ERRORSCNV_CONTROL_TOTALS


Customer Master Load

Parameters APIs Involved Hz_Party_V2Pub.Create_Organization

Hz_Party_V2Pub.Create_PersonHz_Location_V2Pub.Create_LocationHz_Cust_Account_V2Pub.Create_Cust_AccountHz_Customer_Profile_V2Pub.Create_Cust_Profile_AmtHz_Party_Site_V2Pub.Create_Party_SiteHz_Cust_Account_Site_V2Pub.Create_Cust_Acct_SiteHz_Cust_Account_Site_V2Pub.Create_Cust_Site_UseHz_Party_Contact_V2Pub.Create_Org_ContactHz_Cust_Account_Role_V2Pub.Create_Cust_Account_RoleHz_Cust_Account_Role_V2Pub.Create_Role_ResponsibilityHz_Contact_Point_V2Pub.Create_Email_Contact_PointHz_Contact_Point_V2Pub.Create_Phone_Contact_PointHz_Relationship_V2Pub.Create_RelationshipHz_Cust_Account_V2Pub.Create_Cust_Acct_RelateArp_Bank_Pkg.Process_Cust_Bank_Account



SR. No.







Interface Tables:

Base Tables:


Field Mapping detailsSource Table Name : CNV_HZ_PARTIES

Source Table Name : CNV_HZ_LOCATIONS

Source Table Name : CNV_HZ_ORG_PROFILES

Source Table Name : CNV_HZ_PERSON_PROFILES

Source Table Name : CNV_HZ_CUST_ACCOUNTS

Source Table Name : CNV_HZ_CUSTOMER_PROFILES

Source Table Name : CNV_HZ_PARTY_SITES

Source Table Name : CNV_HZ_CUST_ACCT_SITES

Source Table Name : CNV_HZ_CUST_SITE_USES


Source Table Name : CNV_HZ_CONTACT_POINTS

Source Table Name : CNV_HZ_PARTY_REL_TYPES

Source Table Name : CNV_HZ_RELATIONSHIPS

Source Table Name : CNV_HZ_CUST_ACCT_RELATE

Source Table Name : CNV_RA_TAX_EXEMPTIONS

Source Table Name : CNV_XREF_CUSTOMERS

Source Table Name : CNV_AP_CREDIT_CARD_ACCOUNTS

Source Table Name : CNV_PARTY_BATCHES


Source Table Name : CNV_CONVERSION_ERRORS

Source Table Name : CNV_CONTROL_TOTALS

Validation LogicTo Load Customer Master Data

Begin Select the Count of records in CNV_HZ_PARTIES table and calculate the number of batches to be processed(based on the number of records per batch entered while submitting the Customer Load program) For each batch id LOOP INSERT batch id and orig_system_cust_reference INTO cnv_party_batches /* Submit the Customer Master Conversion Program */ l_ar_request_id : = apps.fnd_request.submit_request (application => 'AR',

program => 'CNVCML',argument1 => l_batch_id );

/*Check whether the concurrent program fails or not*/ IF l_ar_request_id = 0 THEN errbuff := 'Failure in submitting the Customer Master Program for Batch Id '| l_batch_id;retcode := 2; END IF; END LOOP;END; Customer Master Conversion program :

BEGIN/* Declare local variables */l_target_num_of_records NUMBER;l_error_num_of_records NUMBER;Begin/* Count the total number of records to be processed */ SELECT Count (*) INTO l_ target_num_of_records FROM CNV_HZ_LOCATIONS WHERE STATUS_FLAG = ‘I’

SELECT Count (*) INTO l_ target_num_of_records


FROM CNV_HZ_PARTIES WHERE STATUS_FLAG =’I’

SELECT Count (*) INTO l_ target_num_of_records FROM CNV_HZ_CUST_ACCOUNTS WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_CUSTOMER_PROFILES WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_CUST_PROFILE_AMOUNTS WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_CONTACT_POINTS WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_CUST_ACCT_SITES WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_CUST_SITE_USES WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_PARTY_REL_TYPES WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_RELATIONSHIPS WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_HZ_CUST_ACCT_RELATE WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_ target_num_of_records FROM CNV_RA_TAX_EXEMPTIONS WHERE STATUS_FLAG = ‘I’

/* Pseudo code for PL/SQL Program to call APIs */

Define cursor for Party Info from Staging table CNV_HZ_PARTIES, CNV_HZ_ORG_PROFILES

where orig_system_reference is in cnv_party_batches corresponding to the batch_id passed

Define cursor for Customer Account Information ( from Staging Table CNV_HZ_CUST_ACCOUNTS_ALL) where orig_system_reference is in cnv_party_batches corresponding to the batch_id passed

Define cursor for Org Contacts, Cust Acct Roles, Role responsibility and Contact Points information (from Staging Table CNV_HZ_CONTACT_POINTS) where substr(orig_system_reference,1,8) is in cnv_party_batches corresponding to the batch_id passed

Define cursor for Party Relationship Type Information from Staging Table CNV_HZ_PARTY_REL_TYPES where orig_system_reference is in cnv_party_batches corresponding to the batch_id passed

Define cursor for Party Relationships Information from Staging Table CNV_HZ_RELATIONSHIPS where orig_system_reference is in cnv_party_batches corresponding to the batch_id passed

Define cursor for Customer Account Site Information from Staging Table CNV_HZ_CUST_ACCT_SITES


where substr(orig_system_reference,1,8) is in cnv_party_batches corresponding to the passed batch_id.

Define cursor for Customer Site Uses Information from Staging Table CNV_HZ_CUST_SITE_USES Where substr(orig_system_reference,1,8) is in cnv_party_batches corresponding to the batch_id passed

Define cursor for Location Information from Staging Table CNV_HZ_LOCATIONS where Substr(orig_system_reference,1,8) is in cnv_party_batches corresponding to the batch_id passed

Define cursor for Cust Relationships information from staging table CNV_HZ_CUST_ACCT_RELATE Where Substr(orig_system_reference,1,8) is in cnv_party_batches

Define cursor for Tax Exemptions from staging table CNV_RA_TAX_EXEMPTIONS Where Substr (orig_system_reference, 1, 8) is in cnv_party_batches corresponding to the batch_id passed

BeginIf l_target_num_of_records > 0 (count of Location records ) THEN

/* To validate and populate Location Information */ Open Location Cursor Loop begin Fetch Data from Staging Tables Call API hz_location_v2pub.create_location to populate Location information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value 'E' Else

Update Staging table with status = ‘P’End if;

EndEnd loopClose cursorEnd if;

If l_target_num_of_records > 0 (count of Party records ) THEN

/* To validate and populate Party Information */ Open Party Cursor Loop begin Fetch Data from Staging Tables

If party_type= ‘ORGANIZATIONPick corresponding data from CNV_HZ_ORG_PROFILES

Call API hz_party_v2pub.create_organization to create party of type ‘ORGANIZATION’Elsif party_type = ‘PERSON’ Pick data from CNV_HZ_PERSON_PROFILES

Call API hz_party_v2pub.create_person to create party of type PERSONEnd if

If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value 'E' Else



EndEnd loopClose cursorEnd if;

If l_target_num_of_records > 0 (count of Cust Account records ) THEN

/ * Validate and populate Cust Account Imformation */

Open Customer Account Cursor loop

begin Fetch and Validate Data from Staging Tables If data passes thru validation

Populate the columns cust_prof_cons_inv_flag and cust_prof_cons_inv_type,profile_class_idinto the customer profile rec. All other columns are NULL.

Call API hz_cust_account_v2pub.create_cust_account to populate Party and Customer Account information and Profile Information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ End if Else Update Staging table with status = ‘P’ End if End End loop Close Customer Account cursor

/Load Profile Amounts */

Open Customer Profile Amount Cursor for all successfully processed customers.loop begin Fetch and Validate Data from Staging Tables If data passes thru validation Call API Hz_Customer_Profile_V2pub.create_cust_profile_amt to populate Profile Amount information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else


End if End End loop Close cursorEnd if;

/* Validate and populate Customer Account Site Information */If l_target_num_of_records > 0 (count of Cust Acct Site records ) THENOpen Cursor for Customer Account Site Information


loop begin Fetch and Validate Data from Staging Tables If data passes thru validation

Call API hz_party_site_v2pub.create_party_site to populateParty Site information to base tables.

Call API hz_cust_acct_site_v2pub. Create_cust_acct_site to populate Customer Account Site information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else



/* Validate and populate Customer Site Uses Information */If l_target_num_of_records > 0 (count of Site Use records ) THENOpen Cursor for Customer Site Uses Information loop

begin Fetch and Validate Data from Staging Tables If data passes thru validation Call API hz_cust_acct_site_v2pub. Create_cust_site_use to populate Customer Site Uses information into base tables End if; If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else

Update Staging table with status = ‘P’ End if; End if End End loop Close cursorEnd if;

/* Validate and populate Party Contacts information */If l_target_num_of_records > 0 (count of Conatct records ) THENOpen Cursor for Org Contacts, Cust Account Roles, Role responsibility and Contact Points Informationloop

begin Fetch and Validate Data from Staging Tables If data passes thru validation

If Conatct person does not exist ,Create a PERSON party rec for the contact person.

Call APIs hz_contact_point_v2pub.create_contact_point hz_cust_account_role_v2pub.create_cust_account_role hz_cust_account_role_v2pub. create_role_responsibility


to populate Party Contact information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else Update Staging table with status = ‘P’

End if; End if End End loop Close cursorEnd if;

/* Validate and populate Party Relationship Types */If l_target_num_of_records > 0 (count of Rel type records ) THENOpen Cursor for Party Relationship Types Information loop

begin Fetch and Validate Data from Staging Tables If data passes thru validation Call API hz_relationship_type_v2pub.create_relationship_type to populate Party Relationship Type information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else

Update Staging table with status_flag = ‘P’End if;


If l_target_num_of_records > 0 (count of Relationship records ) THEN Open Cursor for Party Relationships Information loop

begin Fetch and Validate Data from Staging Tables If data passes thru validation Call API hz_relationship_v2pub.create_relationship to populate Party Relationships information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else




End;

If l_target_num_of_records > 0 (count of Cust Account Relationship records ) THEN Open Cursor for Cust Account Relationships Information loop

begin Fetch and Validate Data from Staging Tables If data passes thru validation Call API HZ_CUST_ACCOUNT_V2PUB.create_cust_acct_relate to populate Cust Account Relationships information into base tables If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else


End if End End loop Close cursorEnd if;End;

/* Validate and populate Tax Exemption */If l_target_num_of_records > 0 (count of Tax Exemption records ) THEN Open Cursor for Tax Exemptions Information loop

begin Fetch and Validate Data from Staging Tables If data passes thru validation Insert Cust Account Relationships information into base table If error encountered then insert error number and error message in Staging Table Update status flag in Staging table with value ‘E’ Else


End if End End loop Close cursorEnd if;End;

/* Cross Reference Load */For successfully loaded site use recordsPick the legacy numbers from staging tables and load the legacy values and Oracle idsInto the CNV_XREF_CUSTOMERS table.

End;

Load Credit Card Accounts


SELECT Count(*) INTO l_target_num_of_recordsFROM CNV_AP_CREDIT_CARD_ACCOUNTSWHERE STATUS_FLAG = ‘I’

Define cursor to select credit card information from staging table CNV_AP_CREDIT_CARD_ACCOUNTS

If l_target_num_of_records > 0 THEN Call the API ARP_BANK_PKG.process_cust_bank_account to Populate Credit Card Accounts Update the base table to populate other related information. If error encountered then insert error number and error message in Error Table Update status flag in Staging table with value ‘E’ Else Update Staging table with status = ‘P’ End if;END IF;

SELECT Count(*) INTO l_target_num_of_recordsFROM CNV_AP_CREDIT_CARD_ACCOUNTSWHERE STATUS_FLAG = ‘E’

INSERT INTO CNV_CONTROL_TOTALS ( PROGRAM_NAME , START_TIME , END_TIME , TARGET_RECORDS, ERROR_RECORDS , SEGMENT1 , SEGMENT2 , SEGMENT3 ) VALUES ( ‘Credit Card Load’, l_Start_time, l_End_time, l_target_num_of_records, l_err_num_of_records, "", "", "", );






S.T. CNV_HZ_PARTIESError in getting Profile Record from cnv_hz_org_profiles table ORA-01403: no data found

There is no related data in the cnv_hz_org_profiles for the Original System Reference of the party of type ‘Organisation’

There has to be a record in cnv_hz_org_profiles table for each record of cnv_hz_parties of party type as ‘Organization’

S.T. CNV_HZ_CUST_ACCT_SITESORIG_SYSTEM_LOC_REF ORA-01403: no data found

Location Id Not found in HZ_LOCATIONS table for the ORIG_SYSTEM_LOC_REF used in the staging table.

Create a location for the ORIG_SYSTEM_LOC_REF used.

S.T. CNV_HZ_CUST_ACCT_SITESORIG_SYSTEM_CUST_REF ORA-01403: no data found

Customer Not Found in HZ_CUST_ACCOUNTS_ALL table for the given ORIGINAL_SYSTEM_CUST_REF used in the staging table.

Create a customer accounts for the given ORIGINAL_SYSTEM_CUST_REF used in the staging table.

S.T. CNV_HZ_CUST_SITE_USESORIG_SYS_BILL_TO_SITE_USE_REFORA-01403: no data found.

Site Use Id for Bill_To locations is not found in HZ_CUST_SITE_USES_ALL table for the given orig_bill_to_site_use_ref in the staging table.

Create a Bill_To site for the given orig_bill_to_site_use_ref used in the staging table.

S.T. CNV_HZ_CUST_SITE_USESORIG_SYSTEM_CUST_ACCT_SITE_REFORA-01403: no data found.

Customer Account Site is not found in HZ_CUST_ACCT_SITES for the given orig_system_cust_acct_site_ref in the staging table.

Create a customer account site for the given orig_system_cust_acct_site_ref used in the staging table.

S.T. CNV_HZ_RELATIONSHIPSSubject ID and object ID cannot refer to the same party ID.

The standard API finding the SUBJECT_ID and OBJECT_ID same while loading the data.

S.T. CNV_HZ_RELATIONSHIPSOBJECT_ID_ORIG_SYSTEM_REFORA-01403: no data found

OBJECT_ID_ORIG_SYSTEM_REF used in the staging table dosen’t exists in HZ_PARTIES table.

Create a party for the given OBJECT_ID_ORIG_SYSTEM_REF in the staging table.

S.T. CNV_HZ_CONTACT_POINTSEither a Raw Phone Number or the Area Code and Phone Number should be passed.

The respective data is not populated in the error column of the staging table.

Populate the data of the said columns in the staging table.

S.T. CNV_HZ_CONTACT_POINTSColumn email_address must have a value

The value is not provided in the email_address column of contact point type as EMAIL.

Populate the email_address column in the


S.T. CNV_HZ_CUST_ACCT_RELATEORIG_SYSTEM_RELATED_CUST_REFORA-01403: no data found

The related customer not found in HZ_CUST_ACCOUNTS_ALL for the given ORIG_SYSTEM_RELATED_CUST_REF in the staging table.

The related customer should be created for establishing a relationship.


PO Vendors



The Oracle PO Vendors’ application is used to record information about companies from whom goods and services are purchased. Vendors are also defined as employees that are reimbursed for expense reports. General vendor information is stored once at the ‘header’ level. If the vendor has multiple sites, the address and contact information will be stored separately for each site under that header. Under each site, there are contacts for each site.

The Oracle base tables for Vendors are PO_VENDORS for Vendor information, PO_VENDOR_SITES_ALL for Vendor Site Information and PO_VENDOR_CONTACTS for Vendor Contact information.

For conversion purposes, the legacy data can be extracted into staging tables, which are identical with the base tables, and then a concurrent program should be fired to take data from staging tables to base tables. The existing records in the oracle base tables can also be updated with the information from staging tables.

Data Category Master

Interface Tables There is no interface table. The user has to create staging tables. The most preferable is to create three staging tables for Vendors, Vendor Sites and Vendor Contacts.


Customized code for importing Vendors. (API is there, but not using that).

Parameters



SR. No.



up and running system admin responsibility/concurrent/manager/administer

2



Database ERD

Interface Tables:


Base Tables:


Different scenarios of using the program / API

Scenario 1: Use of customized code

When using customized code, then there will be only insert/update statements to the three base tables, taking data from the staging tables.

The insertion will follow the following rules;

When a Vendor Site will be inserting, check whether the related Vendor is entered or not. When a Vendor Contact will be inserting, check whether the related Vendor Site is entered or not.

There are unique id fields in the base tables. These id fields can be populated by two ways, firstly the program can take the legacy ids, or secondly the program can use the system sequences to generate ids. The system sequences available are:PO_VENDORS_S (for VENDOR_ID in PO_VENDORS)PO_VENDOR_SITES_S (for VENDOR_SITE_ID in PO_VENDOR_SITES_ALL)PO_VENDOR_CONTACTS_S (for VENDOR_CONTACT_ID in PO_VENDOR_CONTACTS)

The Vendor Number (SEGMENT1) is a unique field. The Vendor number has to be updated in the system, from where the automatic Vendor number is generated. This is to be done using the following code.

UPDATE PO_UNIQUE_IDENTIFIER_CONTROLSET current_max_unique_identifier = (SELECT MAX(TO_NUMBER(segment1)) FROM po_vendors)WHERE table_name = 'PO_VENDORS';

(From Application point of view: In the ‘Payables Manager’ responsibility, the SETUP for the Vendor Number has to be set to ‘Automatic’. Navigation: Setup Options Financials. Go to ‘Suppliers-Entry’ tab.)


Scenario 2: Use of API

Oracle has the API AP_PO_VENDORS_APIS_PKG to load Vendors, Sites and Contact.

This package has procedures to insert Vendors, Sites and Contacts as follows:

procedure insert_new_vendor( p_vendor_name IN VARCHAR2, p_vendor_type_lookup_code IN VARCHAR2 DEFAULT NULL, p_taxpayer_id IN VARCHAR2, p_tax_registration_id IN VARCHAR2, p_women_owned_flag IN VARCHAR2, p_small_business_flag IN VARCHAR2, p_minority_group_lookup_code IN VARCHAR2, p_supplier_number IN VARCHAR2, x_vendor_id OUT NOCOPY NUMBER, x_status OUT NOCOPY VARCHAR2, x_exception_msg OUT NOCOPY VARCHAR2);

procedure insert_new_vendor_site( p_vendor_site_code IN VARCHAR2, p_vendor_id IN NUMBER, p_org_id IN VARCHAR2, p_address_line1 IN VARCHAR2, p_address_line2 IN VARCHAR2, p_address_line3 IN VARCHAR2, p_address_line4 IN VARCHAR2, p_city IN VARCHAR2, p_state IN VARCHAR2, p_zip IN VARCHAR2, p_province IN VARCHAR2, p_county IN VARCHAR2, p_country IN VARCHAR2, p_area_code IN VARCHAR2, p_phone IN VARCHAR2, p_fax_area_code IN VARCHAR2, p_fax IN VARCHAR2, p_email_address IN VARCHAR2, p_purchasing_site_flag IN VARCHAR2 DEFAULT 'N', p_pay_site_flag IN VARCHAR2 DEFAULT 'N', p_rfq_only_site_flag IN VARCHAR2 DEFAULT 'N', x_vendor_site_id OUT NOCOPY NUMBER, x_status OUT NOCOPY VARCHAR2, x_exception_msg OUT NOCOPY VARCHAR2);

procedure insert_new_vendor_contact( p_vendor_site_id IN NUMBER, p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_middle_name IN VARCHAR2, p_prefix IN VARCHAR2, p_title IN VARCHAR2, p_mail_stop IN VARCHAR2, p_area_code IN VARCHAR2, p_phone IN VARCHAR2, p_alt_area_code IN VARCHAR2 DEFAULT NULL,


p_alt_phone IN VARCHAR2 DEFAULT NULL, p_fax_area_code IN VARCHAR2 DEFAULT NULL, p_fax IN VARCHAR2 DEFAULT NULL, p_email_address IN VARCHAR2 DEFAULT NULL, p_url IN VARCHAR2 DEFAULT NULL, x_vendor_contact_id OUT NOCOPY NUMBER, x_status OUT NOCOPY VARCHAR2, x_exception_msg OUT NOCOPY VARCHAR2);

When using these API, the main constraint is, these procedures are giving little options to pass as parameter. If the scenario requires much more information to be converted, then we have to write an update program with those informations after the data are loaded.






Vendor Insert Statement Fails due to unique constraint violation

PO_VENDORS_U1 (Vendor ID)

PO_VENDORS_U2 (Vendor Name)

PO_VENDORS_U3 (Segment1)

There are unique fields in Vendor table. The violation of this uniqueness causes the errors.

Check for uniqueness of the Vendor Id.Check for uniqueness of the Vendor Name.Check for uniqueness of the Vendor Number.

Vendor Site Insert Statement Fails due to unique constraint violation

PO_VENDOR_SITES_U1 (Vendor Site ID)PO_VENDOR_SITES_U2 (Vendor Id, Vendor Site Code, Org Id)

There are unique indexes in Vendor Site table. The violation of this uniqueness causes the errors.

Check for uniqueness of the Vendor Site Id.Check for uniqueness of Vendor Id, Vendor Site Code and Org Id.

Vendor Contact Insert Statement Fails due to unique constraint violation

PO_VENDOR_CONTACTS_U1 (Vendor Contact ID)

There are unique indexes in Vendor Contact table. The violation of this uniqueness causes the errors.

Check for uniqueness of the Vendor Contact Id.







Refer to the Oracle Payables APIs Manual and Oracle Payables User’s Guide on Metalink for the Supplier APIs and functionality.

Refer to the Oracle eTRM website for relationships between the base tables, the required fields and the indexes.


CRM Resources



Oracle 11i provides functionality through the CRM foundations module to store information about an individual or object (Resource) that can be assigned a level of work (Task). These resources will be populated into the Oracle CRM Foundation Resource Manager. Resources can be of several types, e.g. – Employee, Supplier Contact, Party etc.

The resources can be assigned as the Salespersons.

The resources are working within Groups. After creation of resources, they have to be assigned to respective Groups.

For resources, Roles have to be defined. And the roles to be attached to the resources also.

Oracle 11i comes equipped with new Resource Manager APIs. These APIs are used to load the data from the legacy systems. The converted data can be used by Service, Finance and Order to Cash to facilitate Service Calls, Ordering, and Financial Reporting.

Data Category Master

Interface Tables There is no interface table. The user has to create staging tables. The staging tables will be created as per the requisition.


Oracle supported APIs are used to create the several resource related entities.

Parameters



SR. No.



up and running system admin responsibility/concurrent/manager/administer

2


Technical Flow Diagram for the Loading data from staging tables to Oracle base tables:


Database ERD

Staging Tables:


Base Tables:


Different scenarios of using the APIs

List of APIS:

Resource APIs: JTF_RS_RESOURCE_PUB.Create_Resource (for creating resources)JTF_RS_RESOURCE_PUB.Update_Resource (for updation of existing resources)

Group APIs: JTF_RS_GROUP_PUB.Create_Resource_Group (for creating resource groups)JTF_RS_GROUP_PUB.Update_Resource_Group (for updation of existing resource groups)

JTF_RS_GROUP_MEMBERS_PUB.Create_Resource_Group_Members (for creating group members)

Salesrep APIs: JTF_RS_SALESREPS_PUB.Create_Salesrep (for creating sales persons)JTF_RS_SALESREPS_PUB.Update_Salesrep (for updation of existing sales person)

Roles APIs: JTF_RS_ROLES_PUB.Create_Rs_Resource_Roles (for creating roles)JTF_RS_ROLES_PUB.Update_Rs_Resource_Roles (for updation of existing roles)

JTF_RS_ROLE_RELATE_PUB.Create_Resource_Role_Relate (for creating resource role relation)

Resource Conversion:

Resources can be of following types:

Employee Supplier contact Party Partner Other TBH

The Create_Resource API creates resources of all the above resource categories. Except for the categories ‘Other’ and ‘TBH’, all the other resources are referred from respective bases, i.e., the ‘Employee’ resource should be an existing employee, the ‘Supplier Contact’ resource will be an existing vendor contact etc.

For the resources of categories ‘Employee’, ‘Supplier Contact’, ‘Party’ and ‘Partner’, it is required to pass some identification of the entity during the creation of resource. The parameter P_SOURCE_ID is used for that. If a resource be of category ‘Employee’ then the PERSON_ID from table PER_ALL_PEOPLE_F of that employee should be passed as a value to this parameter. Similarly, we have to pass the VENDOR_CONTACT_ID from PO_VENDOR_CONTACTS for Supplier Contact, PARTY_ID from HZ_PARTY_SITES for Party, PARTY_ID from JTF_RS_PARTNERS_VL for Partners.

Salesrep Conversion:

Salespersons are resources, who deal with the sales. Once the resource is created, that resource can be assigned as a sales person by the Create_Salesrep API.


Group Conversion:

Resource Group is a logical cluster, which is doing some work. Resources are entered into the group as Group Members. Groups are created using the Create_Resource_Group API. Then resources are assigned to groups as Group Members using the Create_Resource_Group_Members API.

Group usages can be created by simply inserting records into JTF_RS_GROUP_USAGES.

Role Conversion:

Role is to determine the role of a resource or of a group. Role can be created using the Create_Rs_Resource_Roles API. After creation of roles, the roles can be assigned to resources or groups using the Create_Resource_Role_Relate API.







Refer to the CRM API Reference Guide Resource APIs and functionality.

Refer to the Oracle eTRM website for relationships between the base tables, the required fields and the indexes.



Accounting Period – The fiscal period a company uses to report financial results, such as a calendar month or fiscal period.






Bill of Material (BOM) – A list of component items associated with a parent item and information about how each item relates to the parent item. Oracle Manufacturing supports standard, model, option class, and planning bills. The item information on a bill depends on the item type and bill type. The most common type of bill is a standard bill of material. A standard bill of material lists the components associated with a product or subassembly. It specifies the required quantity for each component plus other information to control work in process, material planning, and other Oracle Manufacturing functions. Also known as product structures.

BOM Item Type – An item classification that determines the items you can use as components in a bill of material. BOM Item types include standard, model, option class, and planning items.

Category – Code used to group items with similar characteristics, such as plastics, metals, or glass items.

Category Sets – A feature in Inventory where users may define their own group of categories. Typical category sets include purchasing, materials, costing, and planning.



Configuration – A product a customer orders by choosing a base model and a list of options. It can be shipped as individual pieces as a set (kit) or as an assembly (configuration item).

Inventory Controls – Parameter settings that control how Inventory functions.


Inventory Item – Items you stock in inventory. You control inventory for inventory items by quantity and value. Typically, the inventory item remains an asset until you consume it. You recognize the cost of an inventory item as an expense when you consume it or sell it. You generally value the inventory for an item by multiplying the item standard cost by the quantity on hand.

Item Attributes – Specific characteristics of an item, such as order cost, item status, revision control, COGS account, etc.

Item Status – Code used to control the transaction activity of an item.

Locator – Physical area within a subinventory where you store material, such as a row, aisle, bin, or shelf.

Logical Organization – A business unit that tracks items for accounting purposes but does not physically exist.

On-hand Quantity – The physical quantity of an item existing in inventory.

Organization – A business unit such as a plant, warehouse, division, department, and so on. Order Management refers to organizations as warehouses on all Order Management windows and reports.

Physical Inventory – A periodic reconciliation of item counts with system on–hand quantities.

Revision – A particular version of an item, bill of material, or routing.



Set of Books – A financial reporting entity that partitions General Ledger information and uses a particular chart of accounts, functional currency, and accounting calendar. This concept is the same whether or not the Multi–organization support feature is implemented.

Standard Costing – A costing method where a predetermined standard cost is used for charging material, resource, overhead, period close, job close, and cost update transactions and valuing inventory. Any deviation in actual costs from the predetermined standard is recorded as a variance.

Subinventory – Subdivision of an organization, representing either a physical area or a logical grouping of items, such as a storeroom or receiving dock.








Counters Load



Counters conversion is used to move historical meter read data from legacy system to Oracle Service Module.

The conversion process will begin by extracting data from legacy systems and loading to intermediate counters tables.

The scope of this unit of work (Load Conversion) is to extract data from intermediate tables and populate the Oracle base tables. The Oracle Counters Load script extracts data from the intermediate tables and validates data. Upon successful validation the Oracle Counters packages insert routines are called to populate data in the Counter base tables.

The load process will start by submitting the Generic Custom Conversion Concurrent process by giving ‘Counters Load’ as parameter. All Load conversion processes will kickoff by submitting this Generic Conversion program with the appropriate parameters.


Interface Tables CNV_COUNTER_GROUPSCNV_COUNTER_GRP_LOGCNV_COUNTERSCNV_COUNTER_VALUES

Base Tables CS_COUNTER_GROUPSCS_COUNTER_GRP_LOGCS_COUNTERSCS_COUNTER_VALUESCS_CTR_AUDIT



Counters Load

Parameters APIs Involved Cs_Counter_Groups_Pkg.Insert_Row

Cs_Counters_Pkg.Insert_RowCs_Counter_Grp_Log_Pkg.Insert_RowCs_Counter_Values_Pkg.Insert_Row



SR. No.







Interface Tables:

Base Tables:


Field Mapping details

Source Table Name : CNV_COUNTER_GROUPS

Source Table Name : CNV_COUNTERS

Source Table Name : CNV_COUNTER_GRP_LOG

Source Table Name : CNV_COUNTER_VALUES

Validation Logic

BeginDeclare l_counter_group_num_of_records l_counter_grp_log_num_of_records l_counter_num_of_records l_counter_value_num_of_records l_counter_group_num_of_error_records l_counter_grp_log_num_of_error_records l_counter_num_of_error_records l_counter_value_num_of_error_records

Begin/* Count the total number of records to be process */ SELECT Count (*) INTO l_counter_group_num_of_records FROM CNV_COUNTER_GROUPS WHERE STATUS_FLAG = ‘I’

SELECT Count (*) INTO l_counter_grp_log_num_of_records FROM CNV_COUNTER_GRP_LOG WHERE STATUS_FLAG = ‘I’

SELECT Count (*) INTO l_counter_value_num_of_records


FROM CNV_COUNTERS WHERE STATUS_FLAG = ‘I’

SELECT Count(*) INTO l_counter_value_num_of_records FROM CNV_COUNTER_VALUES WHERE STATUS_FLAG = ‘I’

/* Submit the Counters Conversion Program */ l_cs_request_id : = FND_REQUEST.SUBMIT_REQUEST;

IF l_cs_request_id = 0 THEN Print('ERROR: Counters Conversion Program failed'); Print('Please verify the concurrent program.');

RETURN; ELSE

/* Wait till the concurrent program completes */

LOOPl_completed := FND_CONCURRENT.WAIT_FOR_REQUEST;IF dev_phase = 'COMPLETE' THEN

EXIT;END IF;

END LOOP; END IF;

/* Count the total number of records errored */

SELECT Count (*) INTO l_counter_group_num_of_error_records FROM CNV_COUNTER_GROUPS WHERE STATUS_FLAG = ‘E’

SELECT Count (*) INTO l_counter_grp_log_num_of_error_records FROM CNV_COUNTER_GRP_LOG WHERE STATUS_FLAG = ‘E’

SELECT Count (*) INTO l_counter_num_of_error_records FROM CNV_COUNTERS WHERE STATUS_FLAG = ‘E’

SELECT Count(*) INTO l_counter_value_num_of_error_records FROM CNV_COUNTER_VALUES WHERE STATUS_FLAG = ‘E’

/ * Insert the log details into custom control file */

INSERT INTO CNV_CONTROL_TOTALS (PROGRAM_NAME , START_TIME , END_TIME , TARGET_RECORDS, ERROR_RECORDS, SEGMENT1 ,


SEGMENT2 , SEGMENT3 , ) VALUES ( ‘COUNTERS CONVERSION’, l_Start_time, l_End_time, l_counter_group_num_of_records , l_counter_group_num_of_error_records, “COUNTER GROUP", "", "",);

INSERT INTO CNV_CONTROL_TOTALS( PROGRAM_NAME , START_TIME , END_TIME , TARGET_RECORDS, ERROR_RECORDS, SEGMENT1 , SEGMENT2 , SEGMENT3 ,) VALUES( ‘COUNTERS CONVERSION’, l_Start_time, l_End_time, l_counter_group_log_num_of_records , l_counter_group_log_num_of_error_records, “COUNTER GRP LOG", "", "",);

INSERT INTO CNV_CONTROL_TOTALS( PROGRAM_NAME , START_TIME , END_TIME , TARGET_RECORDS, ERROR_RECORDS, SEGMENT1 , SEGMENT2 , SEGMENT3 ,) VALUES( ‘COUNTERS CONVERSION’, l_Start_time, l_End_time, l_counter_num_of_records , l_counter_num_of_error_records, “COUNTERS", "", "",);INSERT INTO CNV_CONTROL_TOTALS( PROGRAM_NAME , START_TIME , END_TIME ,


TARGET_RECORDS, ERROR_RECORDS, SEGMENT1 , SEGMENT2 , SEGMENT3 ,) VALUES(‘COUNTERS CONVERSION’, l_Start_time, l_End_time, l_counter_value_num_of_records , l_counter_value_num_of_error_records, “COUNTER VALUES", "", "", ) END;

/* Pseudo code for PL/SQL Program to call Oracle Counter Package Insert Routines */

Define cursor for Counter Group Information (from Staging table CNV_COUNTER_GROUPS where STATUS_FLAG = ‘I’)

Define cursor for Counter Group Log Information (from Staging Table CNV_COUNTER_GRP_LOG where STATUS_FLAG = ‘I’)

Define cursor for Counters Information (from Staging Table CNV_COUNTERS where STATUS_FLAG = ‘I’)

Define cursor for Counter Values info (from staging table CNV_COUNTER_VALUES where status_flag = ‘I’ and orig_system_ctr_grp_log_id = counter grp log cursor.attribute1)

Declare local variables Begin

/* To validate and populate Counter Group Information */ Open Counter Group Cursor Loop Fetch and Validate Data from Staging Table

Call the package CS_COUNTER_GROUPS_PKG (Insert Routine) to populate Counter Group information into base tables If counter group is successfully loaded

Update staging table CNV_COUNTER_GROUPS with Status Flag = ‘P’ If erroneous records then Update staging table CNV_COUNTER_GROUPS with Status Flag = ‘E’ Insert error message, error number and time stamp in the error log table End Loop; Close Counter Groups cursor

Open Counters cursor Loop Fetch and validate Counters data Call the package CS_COUNTERS_PKG (Insert Routine) to populate Counter information into base tables If successfully processed , update staging table CNV_COUNTERS with Status Flag = ‘P’ If erroneous records

Update status flag for this row in the staging table CNV_COUNTERS with value = ‘E’

Insert error message, error number and time stamp in the error log table End Loop


Close Counters cursor.

Open Counter Group Log cursor Loop

Fetch and validate Counter Group Log data Open Counter values cursor For each combination of this counter group log with corresponding counter value in CNV_COUNTER_VALUES table,

Call the package CS_COUNTER_GRP_LOG_PKG (Insert Routine) to populate Counter Group Log information into base tables If erroneous records

Update status flag for this row in the staging table CNV_COUNTER_GRP_LOG with value = ‘E’ Insert error message, error number and time stamp in the error log table

If successfully processed, Pass the data of CNV_COUNTER_VALUES table and the Call the package

CS_COUNTER_VALUES_PKG (Insert Routine) to populate Counter Values information into base tables

If successfully processed, update staging table CNV_COUNTER_VALUES with Status Flag = ‘P’ If erroneous records Update status flag for this row in the staging table CNV_COUNTER_VALUES with value = ‘E’ Insert error message, error number and time stamp in the error log table.

End Loop Close Counter values cursor

IF group logs are processed for all corresponding counter values of CNV_COUNTER_VALUES update staging table CNV_COUNTER_GRP_LOG with Status Flag = ‘P’ELSE Update staging table CNV_COUNTER_GRP_LOG with Status Flag = ‘E’END IF;

End Loop

Close Group Log cursor






2.


AR Receipts Open & Closed



The object of this automated conversion is to convert the Cash, Applications and adjustment transactions data into the Oracle Receivables module. It will extract the specified data from the Legacy Systems (RBAS) into a custom staging table, transform/translate the legacy data to meet oracle requirements and load the data into the oracle base tables via the custom pl/sql program.

Scope of this design document is extracting data from custom staging table (CNV_AR_RECEIPTS) and after validating for proper data format and business rules using PL/SQL program, load the data into the Oracle Receivables base tables. All records that do not pass data validation will be flagged with error status and an associated error description will be populated in staging table


Interface Tables CNV_AR_RECEIPTS

Base Tables AR_CASH_RECEIPTS_ALLAR_PAYMENT_SCHEDULES_ALLAR_RECEIVABLES_APPLICXATIONSAR_CASH_RECEIPT_HISTORY_ALLAR_RECEIVABLES_TRX_ALL



AR Receipts Load

Parameters APIs Involved CNV_AR_RECEIPTS_PKG (Custom Package)

AR_RECEIPT_API_PUB.APPLYAR_RECEIPT_API_PUB.Create_Cash



SR. No.







Interface Tables:

Base Tables:



Source Table Name : CNV_AR_RECEIPTS

Validation Logic

Pseudo Code for AR Receipts Load Concurrent Program:

Declare the Cursor to select all unprocessed records from CNV_AR_RECEIPTS table where status_flag=’V’.Define required local variablesBegin Assign who columns into local variables Initialize the environment FND_GLOBAL.APPS_INITIALIZE( user_id, Responsibility_ID, Resp_APPl_ID)

Count the total number of records to be process Open the Cursor for loop Check the Required columns are not null Validate the record If Validation fails

Update the Row with error message and Status_flag=’E’ Else

Call the AR_RECEIPTS_API_PUB.Create_And_Apply API for insert into Base tablesEnd If

If Error in API Call Update the Row with error message and Status_flag=’E’

Else Update the Row with Status_flag=’P’

End IfEnd LoopCount the number of error records in CNV_AR_RECEIPTS table where status_flag= ‘E’ 1Insert the log details like start time, end time, error counts etc into custom control table

End


GL Balances Load



The purpose of this conversion is to move historical data from the legacy General Ledger to the new Oracle General ledger.

Since 100% GL data that is needed is present here so there will be no need for a manual conversion process.

Scope of this design document is extracting data from GL_INTERFACE (Data in this interface table is already populated by extraction design) and import the GL data into GL_JE_LINES, GL_JE_HEADERS, GL_JE_BATCHES oracle base tables by kickoff the oracle standard ‘Journal Import’ process and log the ‘number of successful records’, ‘number of failure records’ details into the custom control table ‘CNV_CONTROL_TOTALS’.

The GL load process will start by submitting the Generic Custom Conversion Concurrent process by giving ‘GL Balance’ as parameter.


Interface Tables GL_INTERFACE

Base Tables GL_JE_LINESGL_JE_HEADERSGL_JE_BATCHES

Error Tables CNV_CONTROL_TOTALS


GL Balance Load

Parameters APIs Involved CNV_GL_BALANCE_PKG (Custom Package)



SR. No.







Interface Tables:

Base Tables:


Field Mapping detailsSource Table Name : GL_INTERFACE

Validation Logic

/* Pseudo code for budget upload into Oracle System */Declare a cursor to select the user je source names from GL_INTERFACE table for ‘NEW’ & ‘P’ type statuses.Define required local variablesBegin Assign who columns into local variables Initialize the environment FND_GLOBAL.APPS_INITIALIZE (user_id, Responsibility_ID, Resp_APPl_ID) Open the Cursor for loop Count the total number of records to be process for each user je source name from GL_INTERFACE table. Insert a row for Source name to be Load in gl_interface_control table. /* Submit the Journal Import Request */

l_gl_request_id := apps.fnd_request.submit_request ( application => 'SQLGL' --Application , program => 'GLLEZL' --Program , argument1 => TO_CHAR(l_interface_run_id) --Interface run Id , argument2 => 1 --Set Of Books id , argument3 => 'N' --Post errors to suspense , argument4 => NULL --Start Date , argument5 => NULL --End Date , argument6 => 'N' --Create journal Summary , argument7 => 'N' --Validate DFF );End Loop;Count the number of error records in GL_INTERFACE for status flag like ‘E%’Insert the log details like start time, end time, error counts etc into custom control table.End






GL Budget Load



Budgeting is used to estimate account balances for a specified range of periods. This important function allows each department to project the amount of money that will be necessary to run that department for the determined budget period. The data can be compared to actual balances to control actual and expected expenditures, thereby reducing costs when possible and not allowing costs to escalate out of control.

The Budget Extract conversion will select the current fiscal year budget from the legacy system, transform this data and then load the Oracle Interface table. Finally, the Budget load conversion program will take information from the Budget Open Interface Table (OIT), validate the data and load the Oracle base tables.


Interface Tables GL_BUDGET_INTERFACE

Base Tables GL_BUDGETS

Error TablesConcurrent Program / /APIs

GL Budget Load

Parameters APIs Involved CNV_BUDGETS_PKG (Custom Package)



SR. No.







Interface Tables:

Base Tables:



Validation Logic

/* Pseudo code for budget upload into Oracle System */Declare a cursor to select the budget name, budget entity name, budget entity id, budget version id and set of books id from gl_budget_interface, gl_budget_versions, gl_budget_entities

Define required local variables

Begin Assign who columns into local variables Initialize the environment FND_GLOBAL.APPS_INITIALIZE( user_id, Responsibility_ID, Resp_APPl_ID)

Count the total number of records to be process Open the Cursor for loop Check the Required columns are not null

apps.fnd_request.submit_request (application => 'SQLGL', -- Application program => 'GLBBSU', -- Program argument1 => <set_of_books_id>, -- Set of Books argument2 => 50249, --Acct Key structure identifier argument3 => <budget_name>, -- Budget Name argument4 => <budget_version_id>, -- Budget Version id argument5 => <budget_entity_name>, -- Budget Organization argument6 => <budget_entity_id> ); -- Budget Entity IdIf Error in API Call Update the Row with error message and Status_flag=’E’

Else Update the Row with Status_flag=’P’ End If

End Loop;Count the number of error records in GL_BUDGET_INTERFACEInsert the log details like start time, end time, error counts etc into custom control table

End



Budget - Estimated cost, revenue, labor hours or other quantities for a project or task. Each budget may optionally be categorized by resource. Different budget types may be set up to classify budgets for different purposes. In addition, different versions can exist for each user–defined budget type: current, original, revised original and historical versions. The current version of a budget is the most recently base lined version.

Budget interface table - In Oracle General Ledger, a database table that stores information needed for budget upload.

Budgets upload - In Oracle General Ledger, the ability to transfer budget information from a spreadsheet to General Ledger. For example, with the spreadsheet interface you can upload budget information from your spreadsheet to General Ledger.


AR Invoices Open & Closed



This conversion will extract Invoices, Credit Memos, and Debit Memos from the various legacy systems. The project management team will utilize a phased approach for converting data from the respective Business Units. The general approach will be to extract information from the legacy systems (direct database connection), load a staging area (trying to maximize speed using bulk loading by SQL*Loader), then leveraging Oracle approved methods for loading Oracle base tables (Import programs).

In this case, the conversion will seek to leverage the Oracle AutoInvoice program to select information from the Interface tables to automatically create the necessary transactions in the Oracle Receivables module.


Interface Tables CNV_CONTROL_TOTALSRA_INTERFACE_LINES_ALLRA_INTERFACE_SALESCREDITS_ALLRA_INTERFACE_DISTRIBUTIONS_ALL

Base Tables RA_BATCHES_ALLAR_PAYMENT_SCHEDULES_ALLAR_RECEIVABLE_APPLICATIONS_ALLAR_ADJUSTMENTS_ALLRA_CUSTOMER_TRX_ALLRA_CUSTOMER_TRX_LINES_ALLRA_CUST_TRX_LINE_GL_DIST_ALLRA_CUST_TRX_LINE_SALESREPS_ALL

Error Tables RA_INTERFACE_ERRORS_ALL


AR Invoice Load (RAXMTR)

Parameters APIs Involved CNV_AR_INVOICE_PKG (Custom Package)



SR. No.







Interface Tables:

Base Tables:


Field Mapping detailsSource Table Name : RA_INTERFACE_LINES_ALL

Source Table Name : RA_INTERFACE_SALESCREDITS_ALL

Source Table Name : RA_INTERFACE_DISTRIBUTIONS_ALL

Validation Logic

CREATE OR REPLACE PACKAGE CNV_AR_INVOICE_PKG AS PROCEDURE LOAD_AR_INVOICES (errbuf out VARCAHR2, retcode out VARCHAR2); END CNV_AR_INVOICE__PKG;/

/* This package will execute the submit request */CREATE OR REPLACE PACKAGE BODY CNV_AR_INVOICE_PKGAS PROCEDURE LOAD_AR_INVOICES (errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS CURSOR c_inv IS SELECT batch_source_name, cust_trx_type_id,count(*) batch_total FROM ra_interface_lines_all WHERE interface_status is null AND request_id is null GROUP BY batch_source_name, cust_trx_type_id; l_ar_request_id NUMBER; l_no_of_records NUMBER := 0; l_no_of_err_records NUMBER := 0; l_completed BOOLEAN; o_phase VARCHAR2 (250); o_status VARCHAR2 (250); o_dev_phase VARCHAR2 (250); o_dev_status VARCHAR2 (250); o_message VARCHAR2 (250); l_start_time DATE; l_end_time DATE; l_count NUMBER := 0; l_exception EXCEPTION; l_batch_source_id NUMBER; l_number_of_instance NUMBER; BEGIN /* Get the Start Time */ SELECT SYSDATE INTO l_start_time


FROM dual; /* Get the Number of Instances */ SELECT NVL(FND_PROFILE.VALUE('CNV AR INVOICE INSTANCES'),4) INTO l_number_of_instance FROM DUAL; apps.Fnd_File.put_line (apps.Fnd_File.LOG,' No of Instance: '||l_number_of_instance );

FOR r_inv IN c_inv LOOP BEGIN SELECT rba.batch_source_id INTO l_batch_source_id FROM ra_batches_all rba WHERE rba.batch_source_name= r_inv.batch_source_name; EXCEPTION WHEN OTHERS THEN Errbuff: = 'Error : '||SQLCODE || ' - '||SQLERRM; Retcode: = g_ora_failure; RAISE l_exception; END; /* Submit the Invoice Import Master Program */ /* This Master Program will initiate the import program RAXTRX */ l_ar_request_id := apps.fnd_request.submit_request (application => 'AR', -- Application program => 'RAXMTR', -- Program argument1 => l_number_of_instance -- Number of Instances argument2 => TO_CHAR(l_batch_source_id), -- Batch Source Id argument3 => r_inv.batch_source_name, -- Batch Source Name argument4 => SYSDATE, -- Default Date argument5 => NULL, -- Transaction Flexfield argument6 => r_inv.cust_trx_type_id, -- Transaction Type argument7 => NULL, -- (Low) Bill To Customer Number argument8 => NULL, -- (High) Bill To Customer Number argument9 => NULL, -- (Low) Bill To Customer Name argument10 => NULL, -- (High) Bill To Customer Name argument11 => NULL, -- (Low) GL Date argument12 => NULL, -- (High) GL Date argument13 => NULL, -- (Low) Ship Date argument14 => NULL, -- (High) Ship Date argument15 => NULL, -- (Low) Transaction Number argument16 => NULL, -- (High) Transaction Number argument17 => NULL, -- (Low) Sales Order Number argument18 => NULL, -- (High) Sales Order Number argument19 => NULL, -- (Low) Invoice Date argument20 => NULL, -- (High) Invoice Date argument21 => NULL, -- (Low) Ship To Customer Number argument22 => NULL, -- (High) Ship To Customer Number


argument23 => NULL, -- (Low) Ship To Customer Name argument24 => NULL, -- (High) Ship To Customer Name argument25 => 'Yes', -- Base Due Date on Trx Date argument26 => NULL , -- Due Date Adjustment Days argument27 => 82 -- Org Id ); apps.fnd_file.put_line (apps.fnd_file.LOG, 'Start ..' || l_ar_request_id);

/*Check whether the concurrent program fails or not*/ IF l_ar_request_id = 0 THEN errbuf := 'ERROR : Invoice Import Concurrent Program Failed'; RETURN; ELSE /*Wait till the concurrent program completes*/ LOOP l_completed := apps.fnd_concurrent.wait_for_request (l_ra_request_id, 60, 60, o_phase, o_status, o_dev_phase, o_dev_status, o_message ); l_count := l_count + 1;

IF o_dev_phase = 'COMPLETE' THEN SELECT actual_start_date, actual_completion_date INTO l_start_time, l_end_time FROM apps.fnd_concurrent_requests WHERE request_id = l_ar_request_id; EXIT; ELSIF o_dev_phase = 'ERROR' THEN EXIT; END IF;

IF l_count = 1 THEN EXIT; END IF; END LOOP; END IF; END LOOP; /*Count the total no of errors occurred*/ SELECT COUNT (1) INTO l_no_of_err_records FROM apps.ra_interface_lines_all WHERE NVL(interface_status,'~') <> 'P' AND request_id is null;


/* This procedure will insert data into load control totals table */

CNV_CTRL_TOTALS_PKG.LAOD_CONTROL_TOTALS ('Invoice Import Load', l_start_time, l_end_time, l_no_of_records, l_no_of_err_records, NULL, NULL, NULL);

EXCEPTIONWHEN OTHERS THEN retcode :=g_ora_failure; errbuff :='Error while doing insertion in control table.';

END; COMMIT; EXCEPTION WHEN OTHERS THEN errbuf := 'Others Error ' || '*' || SQLCODE || '*' || SQLERRM; END LOAD_AR_INVOICES;END CNV_AR_INVOICES_PKG;


Purchase Orders



This conversion will import all Open and Partially received purchase orders from various legacy systems into Oracle (all those purchase orders with status not equal to ‘Closed’ or ‘Cancelled’ and having a creation date no more than one year old).

Data will be populated in the Oracle Open Interfaces tables by the Extract developer. This Conversion program will call the ‘Import Standard Purchase Orders’ Program to transfer data from the Open Interface tables into the base tables.


Interface Tables CNV_CONTROL_TOTALSPO_HEADERS_INTERFACEPO_LINES_INTERFACEPO_DISTRIBUTIONS_INTERFACE

Base Tables PO_HEADERS_ALLPO_LINES_ALLPO_LINE_LOCATIONS_ALLPO_DISTRIBUTIONS_ALL

Error Tables PO_INTERFACE_ERRORS


Purchase Order Load

Parameters APIs Involved CNV_PURCHASE_ORDER_PKG (Custom Package)



SR. No.







Interface Tables:

Base Tables:


Field Mapping detailsSource Table Name : PO_HEADERS_INTERFACE

Source Table Name : PO_LINES_INTERFACERefer to Worksheet ‘po_lines_interface’ in the above attached xls.

Source Table Name : PO_DISTRIBUTIONS_INTERFACERefer to Worksheet ‘po_distribution_interface’ in the above attached xls.

Validation LogicSQL Statements :-- Query to get the total number of records to be processed from PO headers interface tableSELECT COUNT(*) INTO l_num_no_of_purchaseorders FROM po_headers_interface WHERE process_code IS NULL;

-- Query to get the total number of records to be processed from PO lines interface tableSELECT COUNT(*) INTO l_num_no_of_lines FROM po_lines_interface WHERE process_code IS NULL;

-- Query to get the total number of records from PO distribution interface table.SELECT COUNT(*) INTO l_num_no_of_distributions FROM po_distributions_interface;

--Get the Operating Unit Id from PO headers interface table.SELECT DISTINCT ORG_ID INTO l_num_operating_unit_id FROM po_headers_interface WHERE ROWNUM = 1;

--Update the Line Location table for the Received Quantity for wholly received POs

UPDATE po_line_locations_all SET quantity_received = quantity, quantity_billed = quantity,

closed_code = 'CLOSED FOR RECEIVING'WHERE attribute11 = 'W';

UPDATE po_line_locations_all SET quantity_billed = quantity,

closed_code = 'CLOSED FOR INVOICE', firm_status_lookup_code = 'Y'

WHERE attribute11 = 'I';

Parameters to be passed to Standard Purchase Order Import:


1. Buyer_ID : Applicable only for creating blanket order agreements and/or quotes, hence

default value passed will be NULL2. Document Type : Default value passed will be ‘STANDARD’3. Document Subtype : Default value passed will be NULL, since it is used only for quotes4. Create Items Flag : Default value passed will be ‘NO’ since we are not

creating/updating items through this program

5. Create Sourcing Rule Flag: Default value passed will be ‘NO’ – since Sourcing rule need not be created for

STANDARD Pos6. Approval Status : Default value passed will be ‘APPROVED’7. Default for release generation method for auto source rules: Default value will be NULL8. Batch ID : Default value will be NULL since we will not be providing BATCH ID

– as the volume of records to be processed is LOW

9. Operating Unit : Default value will be the ORG_ID of the OPERATING UNIT to which the

responsibility calling this program, is assigned. (This value will be provided in the ORG_ID column of the PO_HEADERS_INTERFACE table)

10. Global agreement : Default value will be NULL, since it is used only for BLANKET Pos.

Pseudocode:

Declare required local variablesBegin

Count the total number of records to be processed in PO_HEADERS_INTERFACE table where PROCESS_CODE is NULL.Count the total number of records to be processed in PO_LINES_INTERFACE table where PROCESS_CODE is NULL.Get the total number of records from PO_DISTRIBUTIONS_INTERFACE. Get the Operating Unit Id from PO_HEADERS_INTERFACE table.Call the procedure account generate of CNV_PURCHASE_ORDER_PKG to generate the account.

If Number of Records to be processed is greater than Zero Submit the Concurrent Request for ‘Standard Purchase Order Import’

Wait till the concurrent program completes.Count the number of error records all the interface tables where process_code in PO_HEADERS_INTERFACE is not equal to ‘ACCEPTED’ and insert the same into the control table.

Insert the log details like start time, end time, error counts etc into custom control table.Update the Line Location table for the Received Quantity for wholly received Pos.Update the Line Location table for the Received Quantity for in transit Pos.

EndPseudo code for account generate Procedure :Parameters: P_success_flag : Return ‘Y’, if success.

Declare a Cursor to get the fob,vendor_id,vendor_site_id,po_header_id,item_id,category_id, destination_type_code,destination_organization_id,charge_account_id,accrual_account_id,


delive_to_person_id by joining po_headers_interface x, po_lines_interface y, po_distributions_interface z Where y.interface_header_id = x.interface_header_id AND z.interface_header_id = y.interface_header_id AND z.interface_line_id = y.interface_line_idOpen the Cursor for Loop Call the procedure GET_CCID of CNV_PURCHASE_ORDER_PKG package by passing the appropriate cursor values. Update the charge account id and accrual account id of PO_DISTRIBUTION_INTERFACE table with the values generated from above procedure. End Loop.Pseudo code for GET_CCID Procedure:Parameters:P_category_id : Category Id. P_destination_type_code : Destination type code. P_destination_organization_id : Destination organization id. P_item_id : Item Id. P_deliver_to_person_id : Delivery person id. P_vendor_id : Vendor Id. P_vendor_site_id : Vendor site id P_fob_dsp : Fob P_charge_account : Out parameter to hold the generated charge account.P_accrual_account : Out parameter to hold the generated accrual account. P_error_flag : Out parameter for error flag. P_error_message : Out parameter for error message.

Declare the local variables.Begin Check that Destination organization id, Item Id, Destination type code, Vendor Id, Vendor site id is not null. If the above values are Null, raise an error. If p_fob_dsp IS NULL Get the fob_lookup_code from po_vendor_sites_all table for the parameter vendor site id. Else Log an error message saying “Invalid Vendor Id or Site Id”. End If. If P_Category_id IS NULL Get the category id by joining mtl_system_items_b msib, mtl_item_categories mic and mtl_category_sets_tl mcsl tables WHERE msib.inventory_item_id = mic.inventory_item_id

AND msib.organization_id = mic.organization_idAND mcsl.category_set_id = mic.category_set_idAND mcsl.category_set_name = 'INV: Item Categories'AND msib.organization_id = (SELECT organization_id FROM hr_organization_units WHERE NAME = ' ITEM MASTER')AND msib.inventory_item_id = P_item_id

End If. Get the chart of accounts into a local variable from fnd_id_flex_structures_tl where id_flex_structure_name = ‘ACCOUNTING_FLEXFIELD’.


Call the API Po_Wf_Build_Account_Init._Start_Workflow to get the charge account and accrual account. End


Bill of Material



The Supply Chain Team analyzed the current legacy data sources and deemed these sources incompatible for an automated extract. The conversion approach will be to manually compile all BOMs (Configured items, kits). The expectation of this conversion will be to enable order takers to build/pick configured models. The Bill of Material will consist of PTO models, Option Classes and Kits. The conversion will then leverage the standard Interface tables.


Interface Tables CNV_CONTROL_TOTALSBOM_BILL_OF_MTLS_INTERFACEBOM_INVENTORY_COMPS_INTERFACE

Base Tables BOM_BILL_OF_MATERIALSBOM_INVENTORY_COMPONENTS

Error Tables CNV_CONVERSION_ERRORS


Bill of Material Load

Parameters APIs Involved CNV_BOM_PKG (Custom Package)



SR. No.







Interface Tables:

Base Tables:


Field Mapping detailsSource Table Name : BOM_BILL_OF_MTLS_INTERFACE

Source Table Name : BOM_INVENTORY_COMPS_INTERFACE

Validation LogicSQL Statements :/* Count the total number of records to be process in bom_bill_of_mtls_interface table */ SELECT COUNT (1) INTO l_no_of_bom_records FROM bom_bill_of_mtls_interface WHERE process_flag = 1;

/* Count the total number of records to be process in bom_inventory_comps_interface table*/ SELECT COUNT (1) INTO l_no_of_comp_records FROM bom_inventory_comps_interface WHERE process_flag = 1;

/*Count the total no of errors occurred in bom_bill_of_mtls_interface table */ SELECT COUNT (1) INTO l_no_of_bom_err_records FROM apps.bom_bill_of_mtls_interface WHERE process_flag = 3;

/*Count the total no of errors occurred in bom_inventory_comps_interface table*/ SELECT COUNT (1) INTO l_no_of_comp_err_records FROM apps.bom_inventory_comps_interface WHERE process_flag = 3;

Pseudo Code for Bill of Material Load Concurrent Program

Declare local variables.Begin Get the total number of records to be processed in bom_bill_of_mtls_interface where process flag = 1. Get the total number of records to be processed in bom_inventory_comps_interface where process flag = 1. If number of records to be processed either in in bom_bill_of_mtls_interface or in

bom_inventory_comps_interface is greate than zero Call the apps.bom_open_interface_api.import_bom import program.If the program returns a error code Log the errors into conversion errors tables.End If.

End If. Count the number of error records in bom_bill_of_mtls_interface where process flag = 3.


Count the number of error records in bom_inventory_comps_interface where process flag = 3.

Insert the log details like start time, end time, error counts etc into custom control table for bom bill of material as well as bom inventory components.

End.

Errors and Solutions:


Standard Errors API errors Records will be validated using the standard Oracle Bill of Material Import program. All other data quality will be managed through the Mock Test Fires and only clean data will be manually compiled in the spreadsheet.


PO Receipts (Open)



The objective of this conversion is to move the Open Receipts (ASN) - from Business Application System to Oracle.

The outstanding Vendors receipts data will be extracted from the legacy system and fed into the Open Interface Tables.

This program will migrate Receipts for only those Purchase Orders that already exist in the Oracle system. The scope of this design document is to extract Receipts data from the Open Interface Tables and load to Oracle through the Receiving Transaction Processor.


Interface Tables CNV_CONTROL_TOTALSRCV_HEADERS_INTERFACERCV_TRANSACTIONS_INTERFACE

Base Tables RCV_SHIPMENT_HEADERSRCV_SHIPMENT_LINESRCV_TRANSACTIONS

Error Tables CNV_CONVERSION_ERRORSPO_INTERFACE_ERRORS


Purchase Order Receipts Load

Parameters APIs Involved CNV_OPEN_RECEIPTS_PKG (Custom Package)



SR. No.







Interface Tables:

Base Tables:


Field Mapping detailsSource Table Name : RCV_HEADERS_INTERFACE

Source Table Name : RCV_TRANSACTIONS_INTERFACERefer to Worksheet ‘rcv_transactions_interface’ in the above attached xls.

Validation Logic

Count the total number of records to be processed SELECT Count (*) INTO l_no_of_recordsFROM RCV_HEADERS_INTERFACEWHERE PROCESSING_STATUS_CODE = ’PENDING’ AND VALIDATION_FLAG = ‘Y’;

Count the number of error records in table.SELECT Count (*) INTO l_no_of_err_recordsFROM RCV_HEADERS_INTERFACEWHERE PROCESSING_STATUS_CODE in (‘ERROR’,’PRINT’);

Submit the concurrent request to fire Receiving Transaction Processor (RCVTP)fnd_request.submit_request( application => 'PO' --Application

,program => 'RCVTP' --Program)

Parameters:1. Mode : Default value for this parameter will be ‘Batch’2. Group_ID : Default value for this parameter will be NULL

Logic:Define required local variablesBegin Get the Start time.

Count the total number of records to be processed Submit the concurrent request to fire Receiving Transaction Processor (RVCTP) Check for the Completion of the Concurrent Requests If Concurrent Request is completed

Count the number of error records in RCV_HEADERS_INTERFACE table where PROCESSING_STATUS_CODE

IN (‘ERROR’,’PRINT’) Else

Wait; End if; Get the End time;

Insert the log details like start time, end time, error counts etc into custom control tableEnd;




Invalid dataPurchase Orders not found

Incorrect PO receipt number. Review and correct Purchase Order has to be there if error occurred during PO conversion it must be added manually so that all receipts have a matching PO prior to this conversion


Fixed Asset (Operating)



The Oracle operating assets application is a mechanism for tracking the company’s depreciable assets. Operating assets provide a method to track the following amongst other related items: depreciation of assets, including amount and time left to depreciate, assets by tag numberlease/ownership information

This tracking is done in order to maintain tax values and whereabouts of company’s owned equipment.

All operating assets information is kept and tracked within the RBAS Legacy system. There is one RBAS master table that will identify the assets to convert. It should then be joined to four related tables with only one of them having the same asset in it, containing additional information about the asset for conversion.

Because tracking of operating assets will be done within the Oracle Application in the future, a conversion process will extract operating asset information from the RBAS legacy system and load the data into the Oracle operating assets module. The conversion process will perform a set of validations/ lookups, then load the data to the required Oracle Open interface table that will in turn map to the appropriate Oracle application base tables.

Prior to this conversion, Install base should be converted since the location will be driven from the install base module so as to not have to maintain location information in 2 modules. Item master should also be converted prior in order to have all items in the system prior to referencing them in this conversion.


Interface Tables FA_MASS_ADDITIONS

Base Tables FA_TRANSACTION_HEADERSFA_ADDITIONSFA_ASSET_HISTORYFA_ASSET_INVOICESFA_INVOICE_TRANSACTIONSFA_BOOKSFA_DISTRIBUTION_HISTORYFA_DEPRN_SUMMARYFA_DEPRN_DETAILCNV_FA_MASS_ADDITIONSCNV_CONTROL_TOTALS

Error Tables CNV_CONVERSION_ERRORSPO_INTERFACE_ERRORS


Fixed Asset Operating Load(FAMAPT, FAS824)

Parameters APIs Involved CNV_FA_OPERATING_PKG (Custom Package)



SR. No.




Before run the program, ensure that the following fields contain a value for each row in FA_MASS_ADDITIONS:

o Mass_addition_id (assigned by a sequence)o Description (not null constraint in staging table)o Asset_category_id o Book_type_code o Date_placed_in_serv ice (not null constraint in staging table)o Fixed_assets_cost (not null constraint in staging table)o Payables_units o Fixed_assets_units o Payables_code_combination_ido Expense_code_combination_id o Location_id o Posting_status (assigned a default when inserted in OIT)o Queue_name (assigned a default when inserted in OIT)o Payables_cost (not null constraint in staging table)o Depreciate_flag o Asset_type o Accounting_date (not null constraint in staging table)o Inventorial_date


Interface Tables:

Base Tables:


Field Mapping detailsSource Table Name : CNV_FA_MASS_ADDITIONS

Source Table Name : FA_ADDITIONS

Validation Logic

SQL StatementsCounting the Number of records:

SELECT Count(1) INTO l_no_of_recordsFROM FA_MASS_ADDITIONSWHERE posting_status = ‘POST’AND queue_name = ‘POST’;

Copy to the back up/ history table:

INSERT INTO CNV_FA_MASS_ADDITIONSSELECT * FROM FA_MASS_ADDITIONSWHERE posting_status = ‘POSTED’;

Pseudo code for ‘Fixed Asset Operating Loading Program ‘:

Declare

l_fa_request_id number; l_no_of_records number;l_no_of_err_records number;

Begin

/* Count the total number of records to be process */

SELECT Count(1) INTO l_no_of_recordsFROM FA_MASS_ADDITIONSWHERE posting_status = ‘POST’AND queue_name = ‘POST’;

/* Submit the Mass Additions Program */

l_fa_request_id := FND_REQUEST.SUBMIT_REQUEST(‘FA’, ‘FAMAPT’);

/*Check whether the concurrent program fails or not*/

IF l_fa_request_id = 0 THENPRINT(‘ERROR : Asset Posting Concurrent Program Failed’);;


PRINT(‘Please verify the concurrent program.’);RETURN;

ELSE

/*Wait till the concurrent program completes*/

LOOPl_completed := FND_CONCURRENT.WAIT_FOR_REQUEST;

IF dev_phase = ‘COMPLETE’ THENEXIT;

END IF;END LOOP;

END IF;

/*Count the total no of errors occurred*/

SELECT Count(1) INTO l_no_of_err_recordsFROM FA_MASS_ADDITIONSWHERE posting_status = ‘POST’AND queue_name = ‘POST’;

/* Insert the log details into custom control file */

INSERT INTO CNV_CONTROL_TOTALS(PROGRAM_NAME ,

START_TIME , END_TIME , TARGET_RECORDS , ERROR_RECORDS , SEGMENT1 , SEGMENT2 , SEGMENT3)

VALUES ('FA POST MASS ADDITIONS', l_Start_time, l_End_time, l_no_of_records,

l_no_of_err_records, "",

"", "");

Call the updation program to update the life of the Asset in FA_BOOKS.(CNV_FA_UPDATION_PKG.UPDATE_ASSET_LIFE).

END;

Pseudo code for ‘Fixed Asset Updation Program ‘: DECLARE

Declare Life Cursor to select Asset Id and Asset Life in Months from FA_ADDITIONS_B (the asset life will be in Attribute15).

BEGIN


FOR cursor record in Life CursorLOOP

UPDATE fa_booksSET life_in_months = cursor_record.attribute15WHERE asset_id = cursor_record.asset_id;

UPDATE fa_additions_bSET attribute15 = nullWHERE asset_id = cursor_record.asset_id;

END LOOP;COMMIT;

END;

Pseudo code for Correction Logic:

The mass addition lines in FA_MASS_ADDITIONS, which will be still in ‘POST’ status (POSTING_STATUS), are the erroneous records. The records should be corrected through the interface form and again the conversion program will be run to load the corrected records. After all records in the FA_MASS_ADDITIONS will be in ‘POSTED’ status (POSTING_STATUS), the conversion will be over.

Pseudo code for Backup Creation Logic:

After the conversion will be over, the data in FA_MASS_ADDITIONS will be copied into CNV_FA_MASS_ADDITIONS for back up data.

BeginINSERT INTO CNV_FA_MASS_ADDITIONSSELECT * FROM FA_MASS_ADDITIONSWHERE posting_status = ‘POSTED’;

End;



Mandatory data missing. Mandatory data missing. Check for Mandatory columns before loading.

Appendix Oracle Assets Users Guide Oracle Applications White Paper for Mass Additions Oracle e-TRM on FA_MASS_ADDITIONS, FA_TRANSACTION_HEADERS, FA_ADDITIONS,

FA_ASSET_HISTORY, FA_ASSET_INVOICES, FA_INVOICE_TRANSACTIONS, FA_BOOKS, FA_DISTRIBUTION_HISTORY, FA_DEPRN_SUMMARY, FA_DEPRN_DETAIL

http://metalink.oracle.com/metalink/plsql/ml2_gui.startup

Sales Orders (Open & Closed)

http://metalink.oracle.com/metalink/plsql/ml2_gui.startup



This conversion involves converting sales order related information. All Open (Entered and Booked) sales orders are imported into Oracle Order Management from the legacy systems.

Extracts will be performed against the legacy systems across the various businesses. This will be formatted into a standard format, and transformed from data provided in cross-reference tables (i.e. customer number, part number, UOM, etc.). The formatted data will b populated into Oracle interface tables. The Conversion program calls the standard import programs will create the Order Header and Line information in the Oracle Order Management module.


Interface Tables OE_HEADERS_IFACE_ALLOE_LINES_IFACE_ALLOE_PRICE_ADJS_IFACE_ALLOE_CREDITS_IFACE_ALL

Base Tables OE_ORDER_HEADERS_ALLOE_ORDER_LINES_ALLOE_PRICE_ADJUSTMENTSOE_SALES_CREDITSCNV_CONTROL_TOTALS

Error Tables CNV_CONVERSION_ERRORSOE_PROCESSING_MSGS_VL


Sales Order Load(OEOIMP)

Parameters APIs Involved CNV_SALES_ORDER_PKG (Custom Package)



SR. No.

Setup Name Setup Values

Notes

1. Set up and Configuration in oracle Order management

Example , Payment Terms, Ship Methods, Freight Carriers etc

2. Conversion of prior conversions such as Customer Master, Inventory Items

The Sales Orders conversion will depend on prior conversions such as Customers, Master Inventory Items etc

3. Internal and standard concurrent managers

up and running

system admin resp/concurrent/manager/administer




Interface Tables:


Oe_headers_iface_all

Oe_credits_iface_all oe_price_adjs_iface_all

Oe_lines_iface_all

Base Tables:

Oe_order_headers_all (header_id)

Oe_sales_credits oe_price_adjustments(sales_credit_id) (price_adjustment_id)

Oe_order_lines_all (line_id)



Source Table Name : OE_HEADERS_IFACE_ALL

Source Table Name : OE_LINES_IFACE_ALL

Source Table Name : OE_PRICE_ADJS_IFACE_ALL

Source Table Name : OE_CREDITS_IFACE_ALL

Validation Logic

SQL Statements1. Counting the Number of records to be processed from headers:

SELECT Count (*) INTO l_no_of_recordsFROM OE_HEADERS_IFACE_ALLWHERE ERROR_FLAG IS NULL;

2. Counting the Number of records to be processed from lines:

SELECT Count (*) INTO l_no_of_recordsFROM OE_LINE_IFACE_ALLWHERE ERROR_FLAG IS NULL ;

3. Counting the Number of records errored out from headers:

SELECT Count (*) INTO l_no_of_recordsFROM OE_HEADERS_IFACE_ALLWHERE ERROR_FLAG IS NOT NULL;

4. Counting the Number of records errored out from headers:

SELECT Count (*) INTO l_no_of_records FROM OE_LINES_IFACE_ALLWHERE ERROR_FLAG IS NOT NULL;


Pseudo code for ‘Sales Order Loading Program ‘:

DECLARE Define cursor for updation of INSTALL BASE record with the Order line number.

l_no_of_records NUMBER; l_no_of_ib_records NUMBER; l_no_of_err_records NUMBER; l_no_of_ib_err_records NUMBER; l_oe_request_id NUMBER; errbuff VARCHAR2(100); l_completed BOOLEAN; lo_phase VARCHAR2 (250); lo_status VARCHAR2 (250); lo_dev_phase VARCHAR2 (250); lo_dev_status VARCHAR2 (250); lo_message VARCHAR2 (250); l_start_time DATE; l_end_time DATE; l_no_ofupdate_success NUMBER; l_seq NUMBER;BEGIN

/* Delete the temporary tables and insert interface data into those */

DELETE cnv_oe_headers_iface_all; DELETE cnv_oe_lines_iface_all; DELETE cnv_oe_price_adjs_iface_all; DELETE cnv_oe_credits_iface_all;

INSERT INTO cnv_oe_headers_iface_all SELECT * FROM oe_headers_iface_all; INSERT INTO cnv_oe_lines_iface_all SELECT * FROM oe_lines_iface_all; INSERT INTO cnv_oe_price_adjs_iface_all SELECT * FROM oe_price_adjs_iface_all; INSERT INTO cnv_oe_credits_iface_all SELECT * FROM oe_credits_iface_all; /* Count the total number of records to be processed */

SELECT COUNT (1) INTO l_no_of_records FROM oe_headers_iface_all WHERE NVL (error_flag, 'N') = 'N' AND NVL (ready_flag, 'Y') = 'Y';/* Submit the Order Import Program */

IF l_no_of_records > 0 THEN l_oe_request_id := apps.Fnd_Request.submit_request (application => 'ONT', -- Application program => 'OEOIMP', -- Program argument1 => NULL, -- Order Source argument2 => NULL, --Original System Document Ref argument3 => NULL, -- Operation Code argument4 => 'N', -- Validate Only? argument5 => 1, -- Debug Level argument6 => 4 -- Number of Order Import instances );


END IF;

/*Check whether the concurrent program fails or not*/

IF l_oe_request_id = 0 THEN errbuf := 'ERROR : Sales Order Import Concurrent Program Failed'; RETURN; ELSE /*Wait till the concurrent program completes*/ LOOP l_completed := apps.Fnd_Concurrent.wait_for_request (l_oe_request_id, 60, 60, lo_phase, lo_status, lo_dev_phase, lo_dev_status, lo_message );

IF lo_dev_phase = 'COMPLETE' THEN

/* Capture the start and end time to log into the Control table */

SELECT actual_start_date, actual_completion_date INTO l_start_time, l_end_time FROM apps.fnd_concurrent_requests WHERE request_id = l_oe_request_id;

/* Update the intermediate org id */ BEGIN UPDATE oe_order_lines_all a SET a.intmed_ship_to_org_id = (SELECT TO_NUMBER(b.attribute20) FROM cnv_oe_lines_iface_all b

WHERE b.ORIG_SYS_DOCUMENT_REF = a.ORIG_SYS_DOCUMENT_REF AND b.attribute20 is not null AND b.orig_sys_line_ref = a.orig_sys_line_ref AND b.orig_sys_line_ref IS NOT NULL AND b.ORIG_SYS_DOCUMENT_REF IS NOT NULL) WHERE EXISTS (SELECT TO_NUMBER(c.attribute20) FROM cnv_oe_lines_iface_all c

WHERE c.ORIG_SYS_DOCUMENT_REF = a.ORIG_SYS_DOCUMENT_REF AND c.attribute20 is not null AND c.orig_sys_line_ref = a.orig_sys_line_ref AND c.orig_sys_line_ref IS NOT NULL AND c.ORIG_SYS_DOCUMENT_REF IS NOT NULL); EXCEPTION WHEN OTHERS THEN errbuf := 'ERROR : Sales Order Updation with Intermediate Org Id is


Failed'; EXIT; END;

EXIT; ELSIF lo_dev_phase = 'ERROR' THEN EXIT; END IF; END LOOP; END IF; /*Count the total no of errors occurred*/

SELECT COUNT (1) INTO l_no_of_err_records FROM oe_headers_iface_all WHERE NVL (error_flag, 'N') <> 'N';

/* Insert the log details into custom control file */ cnv_Ctrl_Totals_Pkg.load_control_totals ('Sales Order Upload', l_start_time, l_no_of_records, l_no_of_err_records, NULL, NULL, NULL, errbuf ); COMMIT;

/* Updtaing Installbase record with the Order line number */ BEGIN

/* Find the count of the target records */

SELECT COUNT(*) INTO l_no_of_ib_records FROM csi_item_instances csi WHERE csi.serial_number IS NOT NULL AND csi.last_oe_order_line_id IS NULL;

Open Cursor for IB Updation. LOOP BEGIN UPDATE csi_item_instances csiii SET csiii.last_oe_order_line_id = (SELECT oeol.line_id

FROM oe_order_lines_all oeol WHERE oeol.packing_instructions = rec_c_instances_to_update.serial_number AND trim(oeol.packing_instructions) IS NOT NULL) WHERE csiii.serial_number = rec_c_instances_to_update.serial_number;


l_no_ofupdate_success := l_no_ofupdate_success +1;

IF MOD (l_no_ofupdate_success, 2000) = 0 THEN COMMIT;

END IF; EXCEPTION WHEN OTHERS THEN errbuf := 'Others Error ' || '*' || SQLCODE || '*' || SQLERRM;

SELECT cnv_conversion_errors_s.NEXTVAL INTO l_seq

FROM DUAL;

cnv_Ctrl_Totals_Pkg.load_cnv_errors (l_seq, 'Sales Order Upload - IB Update', 'OE_ORDER_LINES', NULL, NULL, NULL, 'Update of IB record failed' || '*' || 'SqlErr- Mess ' || SQLERRM, errbuf

); END;

END LOOP; -- Close Cursor. COMMIT; l_no_of_ib_err_records := l_no_of_ib_records - l_no_ofupdate_success; cnv_Ctrl_Totals_Pkg.load_control_totals ('Sales Order Upload - IB Update with Order Line ID', l_start_time, l_no_of_ib_records, l_no_of_ib_err_records, NULL, NULL, NULL, errbuf ); END;

EXCEPTION WHEN OTHERS THEN errbuf := 'Others Error ' || '*' || SQLCODE || '*' || SQLERRM;

SELECT cnv_conversion_errors_s.NEXTVAL INTO l_seq FROM DUAL;


cnv_Ctrl_Totals_Pkg.load_cnv_errors (l_seq, 'Sales Order Upload', 'OE_HEADERS_IFACE_ALL', NULL, NULL, NULL, 'Sales Order uplooad Program Failed' || '*' || 'SqlErr- Mess ' || SQLERRM, errbuf ); END;



Invalid Order Source Order Source not setup for the Order Import Process.

Error out Transactions. Setup Order Source in Order Management and reprocess.

Invalid Customer Number or customer does not exist.

Customer does not exist for the customer reference after the customer conversion.

Error out Transaction with Invalid Customer. Need to setup customer reference for the customer record and then reprocess transaction.

Invalid Transaction and Line Type.

Transaction and Line types not setup in Order management or are inactive.

Error out Transaction. Need to setup transaction and line types in Order Management and then reprocess transaction.

Invalid Salesperson Sales person is invalid or does not exist in oracle resource manager.

Error out Transaction. Setup salesperson and territory information using oracle CRM foundation and then reprocess transaction.

Invalid Inventory Item Inventory Item does not exists in Oracle Inventory or is Inactive.

Error out Transaction with Invalid Inventory Item. Need to setup the Inventory Item in oracle Inventory and then process the Transaction.

Invalid Warehouse for the sales order line.

Inventory Organization does not exists in oracle inventory or is inactive

Error out Transaction. Need to setup Inventory Organization in oracle inventory and then reprocess transaction.

Inventory Item does not exists for the Inventory Organization.

Inventory Item is not assigned to the Inventory Organization

Error out Transaction for Inventory Item does not exist in Inventory Organization. Need to Assign Inventory Item to organization and then reprocess transaction.


Invalid price lists or price on Order.

Price Lists does not exist, is inactive or price is invalid.

Error out transaction. Setup up price lists with association to order and reprocess or correct the price if invalid and reprocess.

Item does not exist on Price Lists. Inventory Item is not assigned to the Price lists on the order.

Error out transaction. Add item to price lists and then reprocess transaction.

Required Data on the Order Header and Line is Null

Field values are Null Error out Transaction with error. Update transaction with required values and reprocess

Invalid Data types and data formats

Invalid data types and format Error out Transaction with error. Update transaction and reprocess.


Appendix

Service Contracts



Service Contracts automates the contract process and provides seamless access toinformation contained in Oracle CRM and ERP applications.Using Service Contracts, users can define any combination of services and gainvisibility to their contracts by ensuring that those deliverables are automaticallytracked.This scope of this document is to create a Service Contract Header, Service Contract Lines (Service and Usage), Service Contract Covered Products and Billing Schedule.


Interface Tables XX_SER_CONTRACT_HEADERSXX_SER_CONTRACT_LINESXX_SER_CONT_COVEREDLINESXX_SER_CONT_STREAMLEVELSXX_SER_CONT_SALESCREDITS

Base Tables OKC_K_HEADERS_BOKC_K_HEADERS_TLOKC_K_LINES_BOKC_K_LINES_TLOKC_K_ITEMSOKC_RULES_BOKC_RULE_GROUPS_BOKS_K_SALES_CREDITSOKS_LEVEL_ELEMENTSOKC_K_PROCESSES


OKS_CONTRACTS_PUB.CREATE_CONTRACT_HEADEROKC_CONTRACT_PUB.CREATE_CONTRACT_PROCESSOKS_CONTRACTS_PUB.CREATE_SERVICE_LINEOKS_CONTRACTS_PUB.CREATE_COVERED_LINEOKS_CONTRACTS_PUB.CREATE_BILL_SCHEDULE

Parameters


SR. No.





Interface Tables:

Base Tables:



XX_SER_CONTRACT_HEADERS

XX_SER_CONTRACT_LINES

XX_SER_CONT_COVEREDLINES

XX_SER_CONT_STREAMLEVELS

XX_SER_CONT_SALESCREDITS

Validation Logic

XX_SER_CONTRACT_HEADERS

Column Name Required Validation

STS_CODEY Select lookup_code from fnd_lookups where lookup_type

='OKC_STATUS_TYPE'

QCL_ID Select Id from OKCQA_CHECK_LISTS_TL where short_description=’RFG- Checklist’

SCS_CODE Y select code from okc_subclasses_tl where meaning '& contract Category'

CHR_GROUP SELECT ID FROM OKC_K_GROUPS_BPARTY_ID Y SELECT PARTY_ID FROM HZ_PARTIES BILL_TO_ID HZ_CUST_SITE_USES_ALLSHIP_TO_ID HZ_CUST_SITE_USES_ALLPRICE_LIST_ID Y List_Header_Id from QP_LIST_HEADERS_BACCOUNTING_RULE_TYPE Y Id1 from OKX_RULES_V For ex . 'Immediate', 'MONTHLY - 3

months'INVOICE_RULE_TYPE Y Id1 from OKX_RULES_V For ex. 'Advance Invoice'PAYMENT_TERM_ID Y ID1 from OKX_RECEIVABLES_TERMS_V


TRANSACTION_TYPE ID1 from OKX_CUST_TRX_TYPES_VRENEWAL_TYPE SELECT LOOKUP_CODE FROM FND_LOOKUPS WHERE

LOOKUP_TYPE ='OKC_RENEWAL_TYPE'

XX_SER_CONTRACT_LINES


K_HDR_ID Contract Header Id

LINE_STS_CODE Select lookup_code from fnd_lookups where lookup_type ='OKC_STATUS_TYPE'

CUSTOMER_ID Customer_Id from HZ_CUST_ACCOUNTSLINE_TYPE Populate 'S' for service and 'U' for Usage

LINE_RENEWAL_TYPE SELECT LOOKUP_CODE FROM FND_LOOKUPS WHERE LOOKUP_TYPE ='OKC_LINE_RENEWAL_TYPE'

SRV_ID

Service-> SELECT * FROM OKX_SYSTEM_ITEMS_V WHERE SERVICE_ITEM_FLAG='Y' AND ID2=83Usage -> SELECT * FROM OKX_SYSTEM_ITEMS_V WHERE USAGE_ITEM_FLAG='Y' AND ID2=83

USAGE_TYPE ( For Usage Line only)

'VRT' -> Actual Per Period 'QTY' -> Actual Per Quantity 'FRT' -> Fixed Per Period 'NPR' -> Negotiated Price

XX_SER_CONT_COVEREDLINES


K_ID Contract Header Id ATTACH_2_LINE_ID Contract Line IdCUSTOMER_PRODUCT_ID Instance_Id from CSI_ITEM_INSTANCES

XX_SER_CONT_SALESCREDITS


CTC_ID Id from JTF_RS_SALESREPSALES_CREDIT_TYPE_ID Id1 from OKX_SALES_CRED_TYPES_V

XX_SER_CONT_STREAMLEVELS


OBJECT1_ID1 If Days then 'DAY' , If Month then 'MTH'JTOT_OBJECT1_CODE Populate with OKS_TUOMRULE_INFORMATION_CATEGORY

Populate with SLL

RULE_INFORMATION1 Populate with Sequence like 1,2,3,4RULE_INFORMATION2RULE_INFORMATION3RULE_INFORMATION4 UOM/PeriodRULE_INFORMATION5 NullRULE_INFORMATION6 AmountRULE_INFORMATION7 OffsetRULE_INFORMATION8 Interface


SQL Statements1. Derive and Validate the SCS_CODE

Select code from okc_subclasses_tl where meaning = <contract Category>

2. Validate the STS_CODESelect ‘x’ from okc_statuses_b where code = <status code>

3. Validate id of QA Check ListSelect Id From OKCQA_CHECK_LISTS_TL where Short_description=<RFG- Checklist>

4. Validate the price_list_id Select ID

From OKX_LIST_HEADERS_V Where Name = <price list name>

5. Validate the Payment terms.6. Validate Bill_to_id and Ship_to_id.7. Derive and Validate the Line Type.8. Derive the SIR_ID Select ID1 From OKX_SYSTEM_ITEMS_V Where NAME = <service item name>

Load Program Logic[Document the steps necessary to process the load into the Oracle base tables.]

Procedure LOAD_SER_CONTRACTS_MAIN

BeginGet the System Date Call CNV_FIX_CONTRACT_BILLING procedure for correcting the billing data in staging

table.Count the total number of records to be process in header table.Count the total number of records to be process in Lines table.Count the total number of records to be process in coverLine table.Count the total number of records to be process in Stream Level table.Get the minimum and maximum record header id. Calculate the batch size. Submit the concurrent request for service contracts load.Count the total no of errors occurred in header Count the total number of error records in Lines tableCount the total number of error records in coverLine table.Count the total number of records error out in Stream Level table.Insert the log details into custom control table

End;

Procedure LOAD_SER_CONTRACTS


Define Cursor for XX_SER_CONTRACT_HEADER where status_flag = ‘I’ and record_header_id >=start_id and record_header_id <= end_id ;Define Cursor for XX_SER_CONTRACT_LINES where status_flag = ‘I’;Define Cursor to select all unprocessed records from XX_SER_CONT_COVEREDLINE table where status_flag='I'Define Cursor XX_ser_cont_stream_level where status_flag = ‘I’;Define Cursor for XX_ser_cont_salescredits where status_flag = ‘I’;Define required local variablesBeginStart the cursor for Loop

Prepare header recordDefault contactsOpen Salescredit cursor for Header Id and prepare salescredit record.Call oks_contracts_pub.create_contract_header API to Load the recordIf Success

Update the staging table headers table with Status 'P'Update the sales credit staging table with Status ‘P’Update okc_k_headers_b table with Orig_System_Reference1 and Attribute_Category from staging table. Update the okc_k_headers_tl table with Cognomen.

ElseGet the Error Id and update the conversion error table.Update the staging tables(Header and Salescredit) with Status ‘E’Raise the header exception to skip line records processing

End IF

Create the Process record to load Workflow Name.Call okc_contract_pub.create_contract_process to create Contract Process.

Start the cursor for Loop for Lines Prepare Line RecordCall oks_contracts_pub.create_service_line API to create LinesIf Success

Update the staging table with Status 'P'Update okc_k_lines_b table with Attribute Category and Price Negotiated.IF Line Type = ‘U’

Update the okc_rules_b table for QRE Category with Rule Information1= 2 and Rule_information3= 1

End If;

ElseGet the Error Id and update the conversion error table.Update the staging table with Status ‘E’Raise the header exception to skip line records processing

End IFStart the cursor for Loop for Covered Lines

Call oks_contracts_pub.create_covered_line API to create covered lineIf Success

Update the staging table with Status 'P'Call Rule API to Create Extended Price Rule

ElseGet the Error Id and update the conversion error table.Update the staging table with Status ‘E’


Raise the header exception to skip line records processingEnd IF

End LoopPrepare the Stream Level Line RecordCall oks_contracts_pub.Create_Bill_Schedule API

End LoopUpdate the okc_k_headers_b record with Estimated Amount .

End LoopEnd

Procedure CNV_FIX_CONTRACT_BILLING

Define the cursor for selecting records from XX_SER_CONT_COVEREDLINE table with Status ‘I’.Define the cursor for selecting records from XX_ser_cont_stream_level table.Define variables.

BeginOpen the cursor for XX_SER_CONT_COVEREDLINE

Open the Stream Level cursor IF stream_level_rec.scsl_rule_information3 > 1 THEN

ln_total := ln_total+ rule_information3 * listprice;ELSE

ln_total := ln_total +rule_information6 /listprice * sccl_listprice;END IF;

End Loop;Update rfgcnv_ser_cont_coveredline table with negotiated_amount = round

(ln_total, 2)END LOOP;



Original System Reference not getting Populated API doesn’t Support Write a Update script to update OKC_K_HEADERS_B after creating Header

Attribute Category not getting Populated API doesn’t Support Write a Update script to update OKC_K_HEADERS_B after creating Header


Refer to the Oracle Service Contracts Concepts and Procedures on Metalink for general Service Contracts Information.http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284384

Refer to the Oracle Contracts for Service API Reference Guide on Metalink for technical details on the population of the Oracle base tables.




Refer to the Oracle Contracts Core API Reference Guide on Metalink for technical details on the population of the Oracle base tables.http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?283844

Refer to the Oracle Contracts Core Contracts Model on Metalink for interaction of the data entities of the Oracle base tables.http://etrm.oracle.com/pls/etrmlatest/etrm_fndnav.show_file?n_file_id=181&c_mode=INLINE

Appendix B – Definitions

Warranty – Standard manufacturer’s warranty against manufacturing defects. It does not provide “bumper-to-bumper” coverage. Service codes at the close of a service call will determine whether the parts and labor for the call are billable items. The warranty period is channel-driven.

Service Agreement – A service agreement that is purchased as part of the original hardware transaction that overlays the manufacturer’s warranty and allows “bumper-to-bumper” coverage for the warranty period and full coverage for the remaining time up to the Service Agreements end-of-term. Pricing for Service Agreements is driven by the Customer/Dealers Sales Contracts, which can be discounted to account for the overlap of the original warranty period.







SALES CONTRACTS



Oracle Contracts for Sales has Contracts Core Functionality plus integration to Oracle iStore, Oracle Order Capture and Oracle Order Management. Oracle Sales allows user to automate their sales contracting process. Contracts for Sales assists a company in enhancing its customer interactions by incorporating contract management into the opportunity to cash business flow. Oracle Contracts for Sales takes advantage of existing Oracle sales management applications such as Oracle TeleSales, Sales Online, and iStore to complete the quote to contract to order flow.


Interface Tables XX_OKC_K_HEADERS_BXX_OKC_K_HEADERS_TLXX_OKC_K_PARTY_ROLES_BXX_OKC_CONTACTS

Base Tables OKC_K_HEADERS_BOKC_K_HEADERS_TLOKC_K_LINES_BOKC_K_LINES_TLOKC_K_PROCESSESOKC_K_PARTY_ROLES_BOKC_K_PARTY_ROLES_TLOKC_CONTACTS


OKC_CONTRACT_PUB.CREATE_CONTRACT_HEADEROKC_CONTRACT_GROUP_PUB.CREATE_CONTRACT_GRPNGSOKC_CONTRACT_PARTY_PUB.CREATE_K_PARTY_ROLEOKC_CONTRACT_PARTY_PUB.CREATE_CONTACTOKC_CONTRACT_PUB.CREATE_CONTRACT_PROCESS

Parameters


SR. No.





Interface Tables:

Base Tables:



XX_OKC_K_HEADERS_B

C:\Ricoh\Conversions-Generic Material\tables\XX_OKC_K_HEADERS_B.doc

XX_OKC_K_HEADERS_TL

C:\Ricoh\Conversions-Generic Material\tables\XX_OKC_K_HEADERS_TL.doc

XX_OKC_K_PARTY_ROLES_B

C:\Ricoh\Conversions-Generic Material\tables\XX_OKC_K_PARTY_ROLES_B.doc

XX_OKC_CONTACTS

C:\Ricoh\Conversions-Generic Material\tables\XX_OKC_CONTACTS.doc

Validation Logic

Load Program Logic[Document the steps necessary to process the load into the Oracle base tables.]Pseudo code for ‘ LOAD_SALES_CONTRACT Program ‘:

Define Cursor for RFGCNV_OKC_K_HEADERS_B;Define Cursor for RFGCNV_OKC_K_HEADERS_TL;Define Cursor for RFGCNV_OKC_K_PARTY_ROLES_B;Define Cursor for RFGCNV_OKC_OKC_CONTACTS;

Define required local variablesBeginAssign who columns into local variablesInitialize the environment FND_GLOBAL.APPS_INITIALIZE( user_id, Responsibility_ID, Resp_APPl_ID)Open cursor Headers

Prepare Header recordCall OKC_CONTARCT_PUB.create_contract_header API If error then


Log the error in errors tableElse if success

UPDATE rfgcnv_okc_k_headers_b table with status_flag = 'P'Call okc_contract_group_pub.create_contract_grpngs API to assign a group to Contract

End If;

Open Cursor RFGCNV_CONTRACT_PARTIESCall OKC_CONTRACT_PARTY_PUB.create_k_party_role

(Insert into OKC_K_PARTY_ROLES_B & OKC_K_PARTY_ROLES_TL thro’ API)

If error thenLog the error in errors table

Else if successUPDATE rfgcnv_okc_k_party_roles_b with status_flag = 'P'Call the okc_contract_party_pub.create_contact API to load Contacts information.

End If;

Close Cursor RFGCNV_CONTRACT_PARTIESCall okc_contract_pub.create_contract_process API to create contract process

Close Cursor HeadersCommit;Count the Total successful records loaded into base table for all the tables.End;



Start date and end date are not in chronological order or have invalid values.

The start date is greater then end date. Populate Start_Date and End_Date in chronological order

Your profile option does not allow automaticnumbering.Please ask your system administrator to change the sequential numbering profile.

Profile Option not set properly. Create an additional Responsibility andassigning Sequential Numbering to 'Always Used' at the Responsibility Level and set the Sales Order program to thatResponsibility Level.

Sales Contract are not getting assigned to any Group Standard API for Sales Contract does not support assigning the Contract to Group.

After creating the Contract Header have the logic to assign it to Group using API (okc_contract_group_pub.create_contract_grpngs)



1. Oracle Contracts Core Concepts and Procedures Release 11.5.6


2. Oracle Contracts Core Users Guide Release 11.5.8


3. Oracle Contracts Core Implementation Guide Release 11.5.8


4. Oracle Contracts Core API Reference Guide Release 11.5.7


Appendix B – Definitions

Warranty – Standard manufacturer’s warranty against manufacturing defects. It does not provide “bumper-to-bumper” coverage. Service codes at the close of a service call will determine whether the parts and labor for the call are billable items. The warranty period is channel-driven.

Service Agreement – A service agreement that is purchased as part of the original hardware transaction that overlays the manufacturer’s warranty and allows “bumper-to-bumper” coverage for the warranty period and full coverage for the remaining time up to the Service Agreements end-of-term. Pricing for Service Agreements is driven by the Customer/Dealers Sales Contracts, which can be discounted to account for the overlap of the original warranty period.







data conversions - hand book

Documents

data category

data validation techniques

concurrent program apis

validation logic

interface tables

conversion short description

process flow diagram

field mapping details