item conversion

CV.040 CONVERSION DATA MAPPING

Item, Category & 3PL Overhead Conversion

Author:

Creation Date: May 1, 2006

Last Updated: June 1, 2006

Document Ref: CNV-004

Version: DRAFT 1C

APPROVALS

Name Signature Date

Document Control

Change Record

3

Date Author Version

Change Reference

Reviewers

Name Signature Date

Distribution

Copy No.

Name Location

1 Library Master Project Library2 Project Manager34

Note To Holders:

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

30 of 52File Ref: document.doc (v. DRAFT 1 )

Contents

Document Control..........................................................................................ii

Introduction....................................................................................................1

Purpose....................................................................................................1Background..............................................................................................1Scope and Application.............................................................................1Audience..................................................................................................1Item Conversion Overview......................................................................2Category Assignment Overview..............................................................3Organization Assignment Overview........................................................3Overhead Cost Assignment for 3PL Overview........................................5Source Systems.......................................................................................6Prior to Conversion..................................................................................6

Application Business Object Reference Information....................................8

Conversion Mapping – Item Master Conversion...........................................9

Extract File layout – Apollo Product............................................................28

Extract File layout – Apollo Meeting Fee.....................................................31

Extract File layout - Materialogic................................................................34

Extract File layout – Oracle 11.5.8 (mtl_system_items).............................36

Descriptive Flexfields...............................................................................1

Data Clean-up................................................................................................4

Data Validation..............................................................................................5

Open and Closed Issues for this Deliverable................................................6

Open Issues..............................................................................................6Closed Issues...........................................................................................6

30 of 52File Ref: document.doc (v. DRAFT 1 )

Introduction

Purpose

The Conversion Data Mapping document describes the:

detailed data mapping from the source legacy systems to Oracle Inventory Application for:

Items,

Organization Assignment,

Categories, and

Overhead cost for 3PL organizations (Materialogic, Flow Valley)

file layout to be used for extraction of the data from the source system

Background

The information in this document has been defined as the result of discussions between project staff, I technical staff, and consultants.

Scope and Application

The Conversion Data Mapping identifies for use in designing conversion programs to convert Items, Organization Assignment and Categories:

data sources

target tables and columns

validation

processing

translation

filter

foreign key rules

The onhand balance and FIFO material cost conversions for all inventory organizations will be handled as part of a separate conversion document CNV-007.

Audience

This document is intended for the following individuals:

I conversion project staff

outside consultants

reviewers of data conversion deliverables30 of 52

File Ref: document.doc (v. )

Item Conversion Overview

Extract programs will extract item information from each of the identified source systems.

These initial extract files will be mapped to a template. The template will be based on the MTL_SYSTEM_ITEMS_INTERFACE table, and the column headers (names) will be identical to those in the MTL_SYSTEM_ITEMS_INTERFACE table.

The template (MTL_SYSTEM_ITEMS_INTERFACE_TEMPLATE) is provided for the purpose of consolidating these initial extract files. Not only does this allow for simple mapping from source file to target, but creates a means to review and cleanse consolidated item information.

The consolidated data in the template will then be converted into a pipe “|” delimited file and will be loaded into the MTL_SYSTEM_ITEMS_INTERFACE table.

The Item Import program will be run to import data from the MTL_SYSTEM_ITEMS_INTERFACE table to Oracle base tables.

When importing items through the Item Interface, new items will be created in the

30 of 52File Ref: document.doc (v. )

Item Master Organization or existing items will be assigned to additional organizations. Validation of imported items is done using the same rules as the item definition forms, to ensure valid items.

After loading the interface tables, the Item Import will be run to import the data. The Item Interface assigns defaults, validates included data, and then imports the new items.

Category Assignment Overview

In addition to the item related data extracted to the MTL_SYSTEM_ITEMS_INTERFACE_TEMPLATE, three (3) columns have been added for the Category Set Names and values. These category values will need to be entered manually for each item as they do not exist in any legacy system.

Column names are Inventory, Purchasing and Planning, and this will also be the exact names of the category sets. The Inventory Category Set will have seven (7) segments, the Purchasing Category Set will have five (5) segments and the Planning Category set will have five (5) segments.

The values entered into each of these three columns will be the values for each Category Set Name:

Example:

Inventory PurchasingSaleable.Consumable.Bars.Mini.Chocolate.Default.Default

Print.Forms.Cash Control.Tally.Default

Print.Forms.Cash Control.8 1/2 X 11.Default Saleable.Consumable.Bars.Point.Chocolate.Default.Default

Print.Forms.Cash Control.8 1/2 X 14.Default

During the first portion of the Item Import process, the system populates the Inventory Category Set with “Default.Default.Default.Default.Default.Default.Default’, the Purchasing Category Set with “Default.Default.Default.Default.Default”, and the Planning Category Set with “Default.Default.Default.Default.Default”.

The intention is that the final extract file (based on the template) will overwrite the defaulted categories only if a catalog string (value) exists in the extract file provided. Where there is no value in the extract file, the defaults should not be overwritten.

Organization Assignment Overview

After the conversion of the item master and categories, items must be assigned to inventory organizations for us to be able to perform transactions in these organizations.

An extract file needs to be provided for each item that has already been imported, listing all organization codes to which that item needs to be


assigned.

The extract file will contain two columns. One for the item number, and One for all inventory organization codes separated by a pipe.

Example:

ITEM_NUMBER ORGANIZATION_CODE3117292 002|003|004|005|006|007|008|009


Overhead Cost Assignment for 3PL Overview

An extract program will extract items, their corresponding overhead cost elements and values from InfoPlus.

This initial extract file will be mapped to a template. The template will contain the relevant overhead cost columns and organization assignment information. Required data that does not form part of the extract file will need to be entered manually. The column headers (names) will be identical to those in the MTL_SYSTEM_ITEMS_INTERFACE table.A transaction row must exist for every item/overhead subelement


combination.

The template (MTL_3PL_OVERHEAD_TEMPLATE) not only allows for simple mapping from source file to target, but creates a means to review and update overhead cost information.

The data in the template will then be converted into a pipe “|” delimited file and will be loaded into the MTL_SYSTEM_ITEMS_INTERFACE table.

This load will take place after the Onhand and Material Cost Conversion (CNV-007). The Item Import program will be run to import overhead subelements and associated cost data from the MTL_SYSTEM_ITEMS_INTERFACE table to Oracle base tables. This load will be an update of items that have already been converted. No new items will be converted as part of this overhead update.

Source Systems

This section will describe source systems for the conversion:

Apollo for product items (1679 items)Apollo for meeting fee items (285 items)Infoplus/Materialogic items (1000 items)Oracle 11.5.8 for purchased items (2000 items)

Note: It is presumed that these item extracts can be filtered to reduce the item conversion numbers considerably. As part of the initial extract effort filter rules need to be decided upon.

Example: No Inactive items will be converted, Items that have had no activity within X period of time will not be

converted.

Prior to Conversion

This section will describe assumptions made prior to the conversion:

1. The Oracle Inventory Application has been setup.

2. Item templates referenced in the conversion program have been created and updated and assigned to items in the final extract file.

3. UOM Classes and UOM Codes have been setup.

4. Item Category Sets and valid Category codes have been finalized and set up in Oracle. In particular the following will be required for this conversion:

Inventory, and Purchasing. All of these will need to be verified immediately prior to the

conversion process start as they are working documents subject to continuous change.


5. Before running the 3PL Overhead Update the following MUST have already taken place:

All items will be converted (CNV-004), All categories have been assigned (CNV-004), All organization assignment has taken place (CNV-004), All onhand balances have been converted (CNV-007) All material costs have been converted (CNV-007)

Hence the sequence for conversion will be:

Items (CNV-004), Categories (CNV-004), Organization Assignment (CNV-004), Onhand Balances (CNV-007) Material Cost (CNV-007) Overhead subelements and costs (CNV-004)


Application Business Object Reference Information

In each of the following sections, a table maps the business objects for Item Master to the Oracle Application tables. The foreign key relationships between the Oracle Application tables are also indicated. Column (2) suggests whether the business object is a candidate for programmatic conversion. In column (4), the standard Oracle interface is documented if one exists for facilitating the conversion of a specific business object.

Business Object

Man/Prog

OwnedBy

Open Interface Production Table Name(s)

Foreign Key Table Name(s)

Setup Business ObjectItem Master P INV MTL_SYSTEMS_ITEM_

INTERFACEMTL_SYSTEMS_ITEM

Item Categories

P INV MTL_ITEM_CATEGORIES_INTERFACE

MTL_ITEM_CATEGORIES

Item Revisions P INV MTL_ITEM_REVISIONS_INTERFACE

Transaction Entities


Conversion Mapping – Item Master Conversion

Below is a table mapping the legacy data elements to the Oracle tables and columns. The following processing ID codes are used in the mapping spreadsheet:

<Item Master>PR = Processing Rule

<Item Master>>TR = Translation Rule

<Item Master>FR = Filter Rule

<Item Master>FKR = Foreign Key Rule

<Item Master>DR = Derivation Rule

<Item Master>DV = Default Value Rule


Target Application: MTL_SYSTEM_ITEMS_INTERFACE

Business Object: Item Master

Prepared by: Pedro Bunn Pereira

Template: Rows high lighted in Grey indicate which rows are contained in the attached consolidation template:

(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target

Application Table Column

Target

Column Datatype

Not Null? Default Value

Source: Apollo (Product)

Table:

Column

Source: Apollo (Meeting Fees)

Table:

Column

Source: InfoPlus

Table:

Column

Source: Oracle 11.5.8

Table:

Column

Inventory item identifier

INVENTORY_ITEM_ID NUMBER Derived

Organization identifier

ORGANIZATION_ID NUMBER Derived

Standard Who column

LAST_UPDATE_DATE DATE System

Standard Who column

LAST_UPDATED_BY NUMBER System

Standard Who column

CREATION_DATE DATE System

Standard Who column

CREATED_BY NUMBER System

Standard Who column

LAST_UPDATE_LOGIN NUMBER System

Flexfield summary flag

SUMMARY_FLAG VARCHAR2

Flexfield segment enabled flag

ENABLED_FLAG VARCHAR2

Flexfield segment start date

START_DATE_ACTIVE DATE


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

Flexfield segment end date

END_DATE_ACTIVE DATE

Item description DESCRIPTION VARCHAR2 PC_DESC MFC_DESC DESCRIPTION

Buyer identifier BUYER_ID NUMBERAccounting rule identifier

ACCOUNTING_RULE_ID NUMBER Null

Invoicing rule identifier

INVOICING_RULE_ID NUMBER Null

Key flexfield segment

SEGMENT1 VARCHAR2 PC_NO MFC_NO CONTROL#

Key flexfield segment

SEGMENT2-20 VARCHAR2 Null

Descriptive flexfield structure defining column

ATTRIBUTE_CATEGORY VARCHAR2

Descriptive flexfield segment

ATTRIBUTE1 VARCHAR2


ATTRIBUTE2 VARCHAR2


ATTRIBUTE3 VARCHAR2


ATTRIBUTE4 VARCHAR2


ATTRIBUTE5 VARCHAR2


ATTRIBUTE6 VARCHAR2


ATTRIBUTE7 VARCHAR2


ATTRIBUTE8 VARCHAR2


ATTRIBUTE9 VARCHAR2


ATTRIBUTE10 VARCHAR2



Descriptive ATTRIBUTE12 VARCHAR230 of 52


Company Confidential - For internal use only

(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

flexfield segmentDescriptive flexfield segment






Flag indicating purchasing item

PURCHASING_ITEM_FLAG

VARCHAR2 Item Template

Flag indicating shippable item

SHIPPABLE_ITEM_FLAG VARCHAR2 Item Template

Flag indicating customer orderable item

CUSTOMER_ORDER_FLAG VARCHAR2 Item Template

Flag indicating internally orderable item

INTERNAL_ORDER_FLAG VARCHAR2 Item Template

Flag indicating service item

SERVICE_ITEM_FLAG VARCHAR2 Item Template

Flag indicating inventory item

INVENTORY_ITEM_FLAG


Flag indicating engineering item

ENG_ITEM_FLAG VARCHAR2 Item Template

Flag indicating item is an inventory asset

INVENTORY_ASSET_FLAG VARCHAR2 Item Template

Flag indicating item is purchasable

PURCHASING_ENABLED_FLAG


Flag indicating item is customer orderable

CUSTOMER_ORDER_ENABLED_FLAG VARCHAR2 Item Template

Flag indicating item is internally orderable

INTERNAL_ORDER_ENABLED_FLAG VARCHAR2 Item Template

Sales order transactions flag

SO_TRANSACTIONS_FLAG


Flag indicating item is

MTL_TRANSACTIONS_ENABLED_FLAG



Company Confidential - For internal use only

(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

transactableFlag indicating item is stockable

STOCK_ENABLED_FLAG


Flag indicating item may appear on a BOM

BOM_ENABLED_FLAG VARCHAR2 Item Template

Flag indicating item may be built in WIP

BUILD_IN_WIP_FLAG VARCHAR2 Item Template

Revision quantity control code

REVISION_QTY_CONTROL_CODE

NUMBER Null

Item catalog group identifier

ITEM_CATALOG_GROUP_ID

NUMBER

Flag indicating item catalog complete

CATALOG_STATUS_FLAG VARCHAR2

Flag indicating material shortages should be checked for this item

CHECK_SHORTAGES_FLAG

VARCHAR2 Null

Flag indicating whether item is returnable

RETURNABLE_FLAG VARCHAR2 Item Template

Default shipping organization

DEFAULT_SHIPPING_ORG

NUMBER

Flag indicating item is collateral item

COLLATERAL_FLAG VARCHAR2 Null

Flag indicating whether item is taxable

TAXABLE_FLAG VARCHAR2 Null

Over tolerance receipts processing method code

QTY_RCV_EXCEPTION_CODE VARCHAR2 Null

Allow item description updates on PO lines

ALLOW_ITEM_DESC_UPDATE_FLAG

VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

Flag indicating whether inspection is required upon receipt

INSPECTION_REQUIRED_FLAG

VARCHAR2 Null

Flag indicating supplier receipt is required

RECEIPT_REQUIRED_FLAG VARCHAR2 Null

Purchasing market price

MARKET_PRICE NUMBER

Purchasing hazard identifier

HAZARD_CLASS_ID NUMBER

Flag indicating whether an RFQ (request for quotation) is required

RFQ_REQUIRED_FLAG VARCHAR2 Null

Maximum acceptable over-receipt percentage

QTY_RCV_TOLERANCE NUMBER Null

Unit list price - purchasing

LIST_PRICE_PER_UNIT NUMBER

Purchasing UN (United Nations) number

UN_NUMBER_ID NUMBER Null

Purchase price tolerance percentage

PRICE_TOLERANCE_PERCENT NUMBER Null

Fixed asset category identifier (foreign key FA asset category flexfield)

ASSET_CATEGORY_ID NUMBER Null

Rounding factor used to determine order quantity

ROUNDING_FACTOR NUMBER

Unit of issue UNIT_OF_ISSUE VARCHAR2 NullRequire receipt location to match

ENFORCE_SHIP_TO_LOCATION_CODE

VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

ship-to locationFlag indicating substitute receipts allowed

ALLOW_SUBSTITUTE_RECEIPTS_FLAG VARCHAR2 Null

Flag indicating unordered receipts allowed

ALLOW_UNORDERED_RECEIPTS_FLAG VARCHAR2 Null

Flag indicating express delivery allowed

ALLOW_EXPRESS_DELIVERY_FLAG VARCHAR2 Null

Days before planned receipt that item may be received

DAYS_EARLY_RECEIPT_ALLOWED

NUMBER Null

Days after the planned receipt that item may be received

DAYS_LATE_RECEIPT_ALLOWED

NUMBER Null

Exception processing for early/late receipts

RECEIPT_DAYS_EXCEPTION_CODE VARCHAR2 Null

Default receipt routing

RECEIVING_ROUTING_ID

NUMBER Null

Invoice close tolerance

INVOICE_CLOSE_TOLERANCE

NUMBER Null

Receipt close tolerance

RECEIVE_CLOSE_TOLERANCE

NUMBER Null

Item-level prefix for lot numbers

AUTO_LOT_ALPHA_PREFIX

VARCHAR2

Next auto assigned lot number

START_AUTO_LOT_NUMBER

VARCHAR2

Lot control code LOT_CONTROL_CODE NUMBER

Shelf life code SHELF_LIFE_CODE NUMBERLength of shelf life days

SHELF_LIFE_DAYS NUMBER PC_SHELF_LIFE

Serial number control code

SERIAL_NUMBER_CONTROL_CODE

NUMBER

Next auto assigned START_AUTO_SERIAL_ VARCHAR230 of 52


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

serial number NUMBERItem level alpha prefix for serial numbers

AUTO_SERIAL_ALPHA_PREFIX VARCHAR2

Inventory or supplier source type

SOURCE_TYPE NUMBER Null

Organization to source items from

SOURCE_ORGANIZATION_ID

NUMBER Null

Subinventory to source items from

SOURCE_SUBINVENTORY

VARCHAR2 Null

Expense account EXPENSE_ACCOUNT NUMBEREncumbrance account

ENCUMBRANCE_ACCOUNT

NUMBER Org Default

Subinventory restrictions type

RESTRICT_SUBINVENTORIES_CODE

NUMBER Null

Conversion between weight unit of measure and base unit of measure

UNIT_WEIGHT NUMBER PC_UNIT_WT

Weight unit of measure code

WEIGHT_UOM_CODE VARCHAR2

Volume unit of measure code

VOLUME_UOM_CODE VARCHAR2

Conversion between volume unit of measure and base unit of measure

UNIT_VOLUME NUMBER

Locators restrictions type

RESTRICT_LOCATORS_CODE

NUMBER

Stock locator control code

LOCATION_CONTROL_CODE

NUMBER

Planned shrinkage rate

SHRINKAGE_RATE NUMBER

Days an order may be early before

ACCEPTABLE_EARLY_DAYS

NUMBER


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

rescheduling is recommendedPlanning time fence code

PLANNING_TIME_FENCE_CODE

NUMBER

Demand time fence code

DEMAND_TIME_FENCE_CODE

NUMBER

Lead time lot size LEAD_TIME_LOT_SIZE NUMBER Null

Standard lot size STD_LOT_SIZE NUMBER NullCumulative manufacturing lead time

CUM_MANUFACTURING_LEAD_TIME NUMBER Null

MRP repetitive acceptable overrun rate

OVERRUN_PERCENTAGE NUMBER Null

Calculate ATP flag (y/n) for MRP planning routine

MRP_CALCULATE_ATP_FLAG VARCHAR2 Null

MRP repetitive acceptable rate increase

ACCEPTABLE_RATE_INCREASE NUMBER Null

MRP repetitive acceptable rate decrease

ACCEPTABLE_RATE_DECREASE NUMBER Null

Cumulative total lead time

CUMULATIVE_TOTAL_LEAD_TIME

NUMBER Null

Planning time fence days

PLANNING_TIME_FENCE_DAYS

NUMBER Null

Demand time fence days

DEMAND_TIME_FENCE_DAYS

NUMBER Null

End assembly pegging flag (y/n) for MRP

END_ASSEMBLY_PEGGING_FLAG VARCHAR2 Null

Flag indicating item to be planned as repetitive schedule

REPETITIVE_PLANNING_FLAG

VARCHAR2 Null

Exception control set

PLANNING_EXCEPTION_SET

VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

Type of item BOM_ITEM_TYPE NUMBER Item TemplateFlag indicating whether all shippable components should be picked

PICK_COMPONENTS_FLAG VARCHAR2 Item Template

Assemble to Order flag

REPLENISH_TO_ORDER_FLAG

VARCHAR2 Null

Base item identifier BASE_ITEM_ID NUMBER NullFlag indicating whether item may have components requiring ATP check

ATP_COMPONENTS_FLAG VARCHAR2 Null

Flag indicating ATP must be checked when item is ordered

ATP_FLAG VARCHAR2 Null

Fixed portion of the assembly's lead time

FIXED_LEAD_TIME NUMBER Null

Variable portion of an assembly's lead time

VARIABLE_LEAD_TIME NUMBER Null

Supply locator for WIP transactions

WIP_SUPPLY_LOCATOR_ID

NUMBER Null

Source of WIP material supply

WIP_SUPPLY_TYPE NUMBER Item Template

Supply subinventory for WIP transactions

WIP_SUPPLY_SUBINVENTORY VARCHAR2 Null

Primary unit of measure code

PRIMARY_UOM_CODE VARCHAR2

Primary stocking unit of measure for the item

PRIMARY_UNIT_OF_MEASURE VARCHAR2 Null

Allowed unit of measure

ALLOWED_UNITS_LOOKUP_CODE

NUMBER Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

conversion typeCost of sales account

COST_OF_SALES_ACCOUNT

NUMBER

Sales account SALES_ACCOUNT NUMBERDefault value for include in cost rollup (Y or N)

DEFAULT_INCLUDE_IN_ROLLUP_FLAG VARCHAR2 Null

Material status code

INVENTORY_ITEM_STATUS_CODE

VARCHAR2 PCS_CODE

Inventory planning code

INVENTORY_PLANNING_CODE

NUMBER Null

Planner code PLANNER_CODE VARCHAR2 NullIndicates whether item is planned as manufactured or purchased

PLANNING_MAKE_BUY_CODE

NUMBER Item Template

Fixed lot size multiplier

FIXED_LOT_MULTIPLIER

NUMBER Null

Rounding control code

ROUNDING_CONTROL_TYPE

NUMBER Null

Annual carrying cost

CARRYING_COST NUMBER 0

Post-processing lead time

POSTPROCESSING_LEAD_TIME

NUMBER

Pre-processing lead time

PREPROCESSING_LEAD_TIME

NUMBER

Full lead time FULL_LEAD_TIME NUMBER

Order cost ORDER_COST NUMBERSafety stock percentage

MRP_SAFETY_STOCK_PERCENT

NUMBER

Safety stock code MRP_SAFETY_STOCK_CODE

NUMBER

Minimum minmax order quantity

MIN_MINMAX_QUANTITY

NUMBER

Maximum minmax order quantity

MAX_MINMAX_QUANTITY

NUMBER

Minimum order MINIMUM_ORDER_QUA NUMBER


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

quantity NTITYFixed order quantity

FIXED_ORDER_QUANTITY

NUMBER

Fixed days supply FIXED_DAYS_SUPPLY NUMBERMaximum order quantity

MAXIMUM_ORDER_QUANTITY

NUMBER

Available to promise rule to be used for this item

ATP_RULE_ID NUMBER Null

Picking rule code PICKING_RULE_ID NUMBER NullHard reservations allowed flag

RESERVABLE_TYPE NUMBER

Percent error above measured quantity

POSITIVE_MEASUREMENT_ERROR NUMBER 0

Percent error below measured quantity

NEGATIVE_MEASUREMENT_ERROR NUMBER 0

Engineering ECN code

ENGINEERING_ECN_CODE

VARCHAR2 Null

Engineering implemented item identifier

ENGINEERING_ITEM_ID NUMBER Null

Engineering implementation date

ENGINEERING_DATE DATE Null

Days after shipment that service begins

SERVICE_STARTING_DELAY NUMBER Null

Flag indicating service for the item is provided by a supplier

VENDOR_WARRANTY_FLAG

VARCHAR2 Null

Indicates whether the item is serviceable by other service-type items and whether

SERVICEABLE_COMPONENT_FLAG

VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

it can be referenced as the component of another itemIndicates whether the item is serviceable by other service-type items and can have service requests reported against it

SERVICEABLE_PRODUCT_FLAG VARCHAR2 Null

Base warranty identifier

BASE_WARRANTY_SERVICE_ID

NUMBER Null

Payment terms identifier

PAYMENT_TERMS_ID NUMBER Null

Indicates whether the item can be a preventive maintenance service item

PREVENTIVE_MAINTENANCE_FLAG VARCHAR2 Null

Primary service specialist

PRIMARY_SPECIALIST_ID

NUMBER Null

Secondary service specialist

SECONDARY_SPECIALIST_ID

NUMBER Null

Serviceable item class identifier

SERVICEABLE_ITEM_CLASS_ID

NUMBER Null

Flag indicating service labor hours are billable

TIME_BILLABLE_FLAG VARCHAR2 Null

Flag indicating service material is billable

MATERIAL_BILLABLE_FLAG VARCHAR2 Null

Flag indicating service expenses are billable

EXPENSE_BILLABLE_FLAG VARCHAR2 Null

Flag indicating cost of service may be prorated

PRORATE_SERVICE_FLAG VARCHAR2 Null

Coverage schedule COVERAGE_SCHEDULE NUMBER Null30 of 52


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

identifier _IDPeriod code for default service duration

SERVICE_DURATION_PERIOD_CODE VARCHAR2 Null

Default service duration

SERVICE_DURATION NUMBER Null

Service supplier identifier

WARRANTY_VENDOR_ID

NUMBER Null

Monetary value of service costs covered by the item

MAX_WARRANTY_AMOUNT

NUMBER Null

Period code for service response time

RESPONSE_TIME_PERIOD_CODE VARCHAR2 Null

Default response time

RESPONSE_TIME_VALUE

NUMBER Null

Indicates how to notify customers of new revisions of the item

NEW_REVISION_CODE VARCHAR2 Null

Flag indicating item may appear on invoices

INVOICEABLE_ITEM_FLAG VARCHAR2 Item Template

Tax code TAX_CODE VARCHAR2 Null

Indicates whether the item can be invoiced

INVOICE_ENABLED_FLAG VARCHAR2 Null

Flag indicating purchases restricted to approved supplier

MUST_USE_APPROVED_VENDOR_FLAG

VARCHAR2 Null

Concurrent Who column

REQUEST_ID NUMBER Null


PROGRAM_APPLICATION_ID

NUMBER Null


PROGRAM_ID NUMBER Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column


PROGRAM_UPDATE_DATE

DATE Null

Flag indicating item may appear on outside operation purchase order

OUTSIDE_OPERATION_FLAG VARCHAR2 Null

Outside operation unit of measure

OUTSIDE_OPERATION_UOM_TYPE

VARCHAR2 Null

Safety stock bucket days

SAFETY_STOCK_BUCKET_DAYS

NUMBER Null

Automatically deletes MPS entries within a time period

AUTO_REDUCE_MPS NUMBER Null

Flag indicating cost information is maintained

COSTING_ENABLED_FLAG VARCHAR2 Item Template

Flag indicating item may be cycle counted

CYCLE_COUNT_ENABLED_FLAG VARCHAR2 Item Template

Demand source line - used for ATO

DEMAND_SOURCE_LINE

VARCHAR2 Null

Inventory item identifier to copy attributes from

COPY_ITEM_ID NUMBER Null

Set identifier - used for ATO

SET_ID VARCHAR2 Null

Item revision REVISION VARCHAR2 NullFlag indicating configuration item automatically created

AUTO_CREATED_CONFIG_FLAG

VARCHAR2 Null

User-defined item type

ITEM_TYPE VARCHAR2

(not used for open interface)

MODEL_CONFIG_CLAUSE_NAME

VARCHAR2 Null

Flag indicating SHIP_MODEL_COMPLE VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

model must be complete to ship

TE_FLAG

MRP Planning option

MRP_PLANNING_CODE NUMBER Null

RMA inspection requirement

RETURN_INSPECTION_REQUIREMENT

NUMBER Null

Demand source type - used for ATO

DEMAND_SOURCE_TYPE

NUMBER Null

Demand source header identifier - used for ATO

DEMAND_SOURCE_HEADER_ID NUMBER Null

Row identifier TRANSACTION_ID NUMBER System

Row status PROCESS_FLAG NUMBER SystemAbbreviation of organization name

ORGANIZATION_CODE VARCHAR2

Concatenated item segments

ITEM_NUMBER VARCHAR2

Concatenated item segments of copied item

COPY_ITEM_NUMBER VARCHAR2 Null

Template identifier TEMPLATE_ID NUMBER Derived

Template name TEMPLATE_NAME VARCHAR2Organization identifier of copied item

COPY_ORGANIZATION_ID NUMBER Null

Abbreviation of organization name of copied item

COPY_ORGANIZATION_CODE VARCHAR2 Null

Type of forecast control for ATO

ATO_FORECAST_CONTROL

NUMBER Null

Transaction type TRANSACTION_TYPE VARCHAR2 System

Material cost MATERIAL_COST NUMBERMaterial sub-element

MATERIAL_SUB_ELEM VARCHAR2

Material overhead rate

MATERIAL_OH_RATE NUMBER

Material overhead MATERIAL_OH_SUB_EL VARCHAR230 of 52


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

sub-element EMMaterial sub-element identifier

MATERIAL_SUB_ELEM_ID

NUMBER Null

Material overhead sub-element identifier

MATERIAL_OH_SUB_ELEM_ID NUMBER Null

Release time fence code

RELEASE_TIME_FENCE_CODE

NUMBER

Release time fence days

RELEASE_TIME_FENCE_DAYS

NUMBER

Flag indicating whether item is a container. Used for shipping sales orders

CONTAINER_ITEM_FLAG VARCHAR2 Null

Flag indicating whether item is a vehicle. Used for shipping sales orders

VEHICLE_ITEM_FLAG VARCHAR2 Null

Maximum load weight of a container or a vehicle that can be used for shipping sales orders

MAXIMUM_LOAD_WEIGHT

NUMBER Null

Minimum fill condition under which a container or vehicle should be used

MINIMUM_FILL_PERCENT NUMBER Null

Container type code for container items. Container types are user definable

CONTAINER_TYPE_CODE VARCHAR2 Null

Internal volume of container items. Used by Shipping

INTERNAL_VOLUME NUMBER Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

to calculate container capacity restrictionsUsed when multiple Item Open Interface processes are run in parallel

SET_PROCESS_ID NUMBER Developer to set

WH_UPDATE_DATE DATE Null

PRODUCT_FAMILY_ITEM_ID

NUMBER Null

PURCHASING_TAX_CODE

VARCHAR2 Null

OVERCOMPLETION_TOLERANCE_TYPE

NUMBER Null

OVERCOMPLETION_TOLERANCE_VALUE

NUMBER Null

Effectivity control code. Indicates if the item is under Date Effectivity or Model/Unit Number Effectivity control.

EFFECTIVITY_CONTROL

NUMBER Null

Global descriptive flexfield structure defining column

GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2 Null

Global descriptive flexfield segment

GLOBAL_ATTRIBUTE1 VARCHAR2 Null











Global descriptive GLOBAL_ATTRIBUTE7 VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

flexfield segmentGlobal descriptive flexfield segment






Over shipment tolerance

OVER_SHIPMENT_TOLERANCE

NUMBER Null

Under shipment tolerance

UNDER_SHIPMENT_TOLERANCE

NUMBER 0

Over return tolerance

OVER_RETURN_TOLERANCE

NUMBER 0

Under return tolerance

UNDER_RETURN_TOLERANCE

NUMBER 0

Flag indicating item is equipment

EQUIPMENT_TYPE NUMBER Null

RECOVERED_PART_DISP_CODE

VARCHAR2 Null

DEFECT_TRACKING_ON_FLAG

VARCHAR2 Null

USAGE_ITEM_FLAG VARCHAR2 Null

EVENT_FLAG VARCHAR2 Null

ELECTRONIC_FLAG VARCHAR2 Null

DOWNLOADABLE_FLAG

VARCHAR2 Null

VOL_DISCOUNT_EXEMPT_FLAG

VARCHAR2 Null

COUPON_EXEMPT_FLAG

VARCHAR2 Null

COMMS_NL_TRACKABLE_FLAG

VARCHAR2 Null

ASSET_CREATION_CODE

VARCHAR2 Null

COMMS_ACTIVATION_REQD_FLAG

VARCHAR2 Null

ORDERABLE_ON_WEB_ VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

FLAG

BACK_ORDERABLE_FLAG

VARCHAR2 Null

WEB_STATUS VARCHAR2 Null

INDIVISIBLE_FLAG VARCHAR2 NullItem Long Description

LONG_DESCRIPTION VARCHAR2 Null

DIMENSION_UOM_CODE

VARCHAR2

UNIT_LENGTH NUMBER

UNIT_WIDTH NUMBER

UNIT_HEIGHT NUMBER

BULK_PICKED_FLAG VARCHAR2 Null

LOT_STATUS_ENABLED

VARCHAR2 Null

DEFAULT_LOT_STATUS_ID

NUMBER Null

SERIAL_STATUS_ENABLED

VARCHAR2 Null

DEFAULT_SERIAL_STATUS_ID

NUMBER Null

LOT_SPLIT_ENABLED VARCHAR2 Null

LOT_MERGE_ENABLED VARCHAR2 NullPenalty incurred for holding this item inventory

INVENTORY_CARRY_PENALTY NUMBER Null

Penalty incurred for time in between two operations

OPERATION_SLACK_PENALTY NUMBER Null

Item is eligible for financing

FINANCING_ALLOWED_FLAG

VARCHAR2 Null

Oracle Internal use only - reserved for future use

EAM_ITEM_TYPE NUMBER Null

Oracle Internal use only - reserved for

EAM_ACTIVITY_TYPE_CODE

VARCHAR2 Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

future useOracle Internal use only - reserved for future use

EAM_ACTIVITY_CAUSE_CODE VARCHAR2 Null


EAM_ACT_NOTIFICATION_FLAG VARCHAR2 Null


EAM_ACT_SHUTDOWN_STATUS VARCHAR2 Null

DUAL_UOM_CONTROL NUMBER Null

SECONDARY_UOM_CODE

VARCHAR2 Null

DUAL_UOM_DEVIATION_HIGH

NUMBER Null

DUAL_UOM_DEVIATION_LOW

NUMBER Null

CONTRACT_ITEM_TYPE_CODE

VARCHAR2 Null

SUBSCRIPTION_DEPEND_FLAG

VARCHAR2 Null

SERV_REQ_ENABLED_CODE

VARCHAR2 Null

SERV_BILLING_ENABLED_FLAG

VARCHAR2 Null

SERV_IMPORTANCE_LEVEL

NUMBER Null

Planned inventory point enabled

PLANNED_INV_POINT_FLAG

VARCHAR2 Null

Lot Translate Enabled

LOT_TRANSLATE_ENABLED

VARCHAR2 Null

Default Sales Order Source Type

DEFAULT_SO_SOURCE_TYPE

VARCHAR2 Null

Create Supply Flag CREATE_SUPPLY_FLAG VARCHAR2 NullSubstitution Window

SUBSTITUTION_WINDOW_CODE

NUMBER Null

Substitution Window Days

SUBSTITUTION_WINDOW_DAYS

NUMBER Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

IB_ITEM_INSTANCE_CLASS

VARCHAR2 Null

CONFIG_MODEL_TYPE VARCHAR2 NullLot Substitution Enabled for 11.5.9

LOT_SUBSTITUTION_ENABLED

VARCHAR2 Null

Minimum License Quantity for 11.5.9

MINIMUM_LICENSE_QUANTITY

NUMBER Null

Activity Source for 11.5.9

EAM_ACTIVITY_SOURCE_CODE

VARCHAR2 Null

LIFECYCLE_ID NUMBER Null

CURRENT_PHASE_ID NUMBER NullCatch-weight Support for 11.5.10

TRACKING_QUANTITY_IND

VARCHAR2 Null

Catch-Weight Support for 11.5.10

ONT_PRICING_QTY_SOURCE

VARCHAR2 Null

Catch-Weight Support for 11.5.10

SECONDARY_DEFAULT_IND

VARCHAR2 Null

Minimum quantity in units

VMI_MINIMUM_UNITS NUMBER Null

Minimum quantity in days

VMI_MINIMUM_DAYS NUMBER Null

Maximum quantity in units

VMI_MAXIMUM_UNITS NUMBER Null

Maximum quantity in days

VMI_MAXIMUM_DAYS NUMBER Null

Fixed order quantity in units

VMI_FIXED_ORDER_QUANTITY

NUMBER Null

SO release authorization flag: 1 - authorization by customer required, 2 - authorization not required (default),

SO_AUTHORIZATION_FLAG NUMBER Null

Consigned flag: 1 - consigned, 2 - unconsigned (default)

CONSIGNED_FLAG NUMBER Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

Auto-expiring ASN flag: 1 - ASN auto-expired (default), 2 - ASN not auto-expired

ASN_AUTOEXPIRE_FLAG NUMBER Null

Forecast type for average daily demand calculation: 1 - order forecast (default), 2 - sales forecast, 3 - historical sales

VMI_FORECAST_TYPE NUMBER Null

Forecast horizon for average daily demand calculation

FORECAST_HORIZON NUMBER Null

1- Yes2- No (default)

EXCLUDE_FROM_BUDGET_FLAG

NUMBER Null

Target Inventory Level Days of Supply

DAYS_TGT_INV_SUPPLY NUMBER Null

Target Inventory Level Calculation Window

DAYS_TGT_INV_WINDOW NUMBER Null

Maximum Inventory Level Days of Supply

DAYS_MAX_INV_SUPPLY NUMBER Null

Maximum Inventory Level Calculation Window

DAYS_MAX_INV_WINDOW NUMBER Null

Distribution Planned

DRP_PLANNED_FLAG NUMBER Null

Critical Component flag

CRITICAL_COMPONENT_FLAG

NUMBER Null

Continuous Inter Organization transfers1. Yes2. No3. Use Global Value (default)

CONTINOUS_TRANSFER

NUMBER Null


(2) (3) (4) (5) (6) (7) (8) (9) (10)

Comments

Target


Target

Column Datatype



Table:

Column


Table:

Column

Source: InfoPlus

Table:

Column


Table:

Column

Convergent Supply Consumption Pattern1. Series2. Parallel3. Use Global Value (default)

CONVERGENCE NUMBER Null

Divergent Supply Feeding Pattern1. Series2. Series with MTQ3. Use Global Value (default)

DIVERGENCE NUMBER Null

CTO 11.5.10 Enhancement

CONFIG_ORGS VARCHAR2 Null

CTO 11.5.10 ENH CONFIG_MATCH VARCHAR2 Null


Target Application: MTL_ITEM_CATEGORIES_INTERFACE

Business Object: Item Category Information


(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Ref.# Comments

Target Application Table Column

Target Column Datatype

Not Null?

Source System File Name

Source System Field Name

Source System Field Datatype

Default Value Validation Rule #

INVENTORY_ITEM_ID NUM Derived

CATEGORY_SET_ID NUM Derived

CATEGORY_ID NUM Derived

LAST_UPDATE_DATE DATE

LAST_UPDATED_BY NUM

CREATION_DATE DATE

CREATED_BY NUM

LAST_UPDATE_LOGIN NUM

REQUEST_ID NUM System

PROGRAM_APPLICATION_ID NUM System

PROGRAM_ID NUM System

PROGRAM_UPDATE_DATE DATE System

ORGANIZATION_ID NUM Derived

TRANSACTION_ID NUM System

PROCESS_FLAG NUM System


(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Ref.# Comments



Not Null?




Default Value Validation Rule #

CATEGORY_SET_NAME VAR2

CATEGORY_NAME VAR2

ORGANIZATION_CODE VAR2

ITEM_NUMBER VAR2

TRANSACTION_TYPE VAR2

SET_PROCESS_ID NUM System


Target Application: MTL_SYSTEM_ITEMS_INTERFACE

Business Object: 3PL Item Overhead Cost Information


(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Ref.# Comments



Not Null?

Default Value




Validation Rule #

Key flexfield segment = item number

SEGMENT1 VARCHAR2

Abbreviation of organization name

ORGANIZATION_CODE VARCHAR2

Material overhead rate MATERIAL_OH_RATE NUMBERMaterial overhead sub-element

MATERIAL_OH_SUB_ELEM

VARCHAR2


Extract File layout – Apollo Product

Below is a table documenting the APOLLO PRODUCT extract file schema. This table is to assist I with the mapping process from the columns contained in each of the initial extract files to the corresponding columns contained in the MTL_system_items_interface and the template (See Conversion Mapping – Item Master Conversion section).

Datafile Field Name Description Datatype

Position:From

Position:To

Oracle Table Oracle Column Description

Apollo Product

pct_no Product catalog type nbr(3) MTL_SYSTEM_ITEMS_INTERFACE

ATTRIBUTE13

Will be a DFF called CATALOG TYPE on the item master with predefined values.

These values MUST match values in Apollo.

Apollo Product

pc_no Product number nbr(12) MTL_SYSTEM_ITEMS_INTERFACE

SEGMENT1 Key Flexfield segment

Apollo Product

pc_desc Product description chr(25) MTL_SYSTEM_ITEMS_INTERFACE

DESCRIPTION Item description

Apollo Product

psd_no

Product storage definition0=Dry1=Frozen2=Refrigerated3=Non=Food

nbr(3) NA NAWill be handled by categories going forward.Not needed for conversion

Apollo Product pc_unit_price Unit price of product nbr(6,3) NA NA

Will be part of Onhand Balances and Cost Conversion CVN007

Apollo Product

pc_case_size Case size of product nbr(4) NA NA Not needed for conversion

Apollo Product

pc_shelf_life Shelf life of product nbr(4) NA NA Length of shelf life days.Not needed for conversion

Apollo pc_unit_wt Unit weight of product nbr(7,3) NA NA Conversion between weight 30 of 52



Position:From

Position:To


Productunit of measure and base unit of measureNot needed for conversion

Apollo Product pcd_no

Product definition0=Non-Food1=Dinner2=Frozen Dessert

nbr(3) NA NA Not needed for conversion

Apollo Product

pc_case_wt Weight of case nbr(6,2) NA NA Not needed for conversion

Apollo Product

pc_case_cube NOT USED nbr(7,2) NA NA Not needed for conversion

Apollo Product

pc_pallet_tie NOT USED nbr(3) NA NA Not needed for conversion

Apollo Product

pc_pallet_high NOT USED nbr(3) NA NA Not needed for conversion

Apollo Product

pc_pallet_total NOT USED nbr(5) NA NA Not needed for conversion

Apollo Product

pcs_code

Product Status1=Active2=Discontinued3=New

nbr(1)

MTL_SYSTEM_ITEMS_INTERFACE

INVENTORY_ITEM_STATUS_CODE Material status code

Apollo Product

pc_short_desc Short description of product

chr(10) NA NA Not needed for conversion

Apollo Product

pcit_no Product type1=Food2=Product

nbr(2) NA NA Not needed for conversion

Apollo Product

pc_bin_location NOT USED chr(5) NA NA Not needed for conversion

Apollo Product

pc_maintain_inv Maintain inventory Y/N chr(1) NA NA Not needed for conversion

Apollo Product

ptt_tax_type NOT USED nbr(4) NA NA Not needed for conversion

Apollo Product

pc_num_days_food NOT USED nbr(3) NA NA Not needed for conversion

Apollo Product

pii_item_id NOT USED chr(3) NA NA Not needed for conversion

Apollo Product

pc_franchise_invoice_yn

NOT USED chr(1) NA NA Not needed for conversion

Apollo Product

pc_isitfood_yn Food Y/N chr(1) NA NA Not needed for conversion

Apollo Product

pc_isitmagsub_yn Magazine subscription Y/N

chr(1) NA NA Not needed for conversion

Apollo Product

ppt_no Champ product type nbr(3) MTL_SYSTEM_ITEMS_INTERFACE

ATTRIBUTE14 Will be a DFF called CHAMP PRODUCT TYPE



Position:From

Position:To


on the item master with predefined values.


DFF must be Mandatory if item is saleable, and Non-Mandatory if the item is non-saleable.

x x MTL_SYSTEM_ITEMS_INTERFACE

PRIMARY_UNIT_OF_MEASURE



WEIGHT_UOM_CODE

Weight unit of measure

x x x TEMPLATE_NAME Template name


Extract File layout – Apollo Meeting Fee

Below is a table documenting the APOLLO MEETING FEE extract file schema. This table is to assist I with the mapping process from the columns contained in each of the initial extract files to the corresponding columns contained in the MTL_system_items_interface and the template (See Conversion Mapping – Item Master Conversion section).


Position:From

Position:To


Apollo Meeting

mfct_no Meeting Fee Catalog Type

nbr(3) MTL_SYSTEM_ITEMS_INTERFACE

ATTRIBUTE13



Apollo Meeting

mfc_no Meeting Fee Number nbr(4) MTL_SYSTEM_ITEMS_INTERFACE


Apollo Meeting

mfc_desc Meeting Fee Description



Apollo Meeting

mfc_paid_attend NA NA

Will not be maintained in OracleNot be needed for conversion

Apollo Meeting

mfc_meeting_fee NA NA


Apollo Meeting

mfc_prepaid_sale NA NA


Apollo Meeting

mfc_free_life NA NA Will not be maintained in Oracle



Position:From

Position:To


Not be needed for conversion

Apollo Meeting

mfc_paid_life NA NA


Apollo Meeting

mfc_enrollment NA NA


Apollo Meeting

mfc_current_member NA NA


Apollo Meeting

mfc_late_member NA NA


Apollo Meeting

mfc_prepaid_member NA NA


Apollo Meeting

mfc_no_of_weeks NA NA


Apollo Meeting

mfc_continuing NA NA


Apollo Meeting

mfc_open_enroll NA NA


Apollo Meeting

mfc_free_choice NA NA


Apollo Meeting

mfc_corp_solutions NA NA


Apollo Meeting

mfc_call_center_viewable

NA NA Will not be maintained in OracleNot be needed for



Position:From

Position:To


conversion

Apollo Meeting

mfc_gift_certificate NA NA


Apollo Meeting

mfc_season_pass NA NA



PRIMARY_UNIT_OF_MEASURE




Extract File layout - Materialogic

Below is a table documenting the MATERIALOGIC extract file schema. This table is to assist I with the mapping process from the columns contained in each of the initial extract files to the corresponding columns contained in the MTL_system_items_interface and the template (See Conversion Mapping – Item Master Conversion section).


Position:From

Position:To


Materialogic Control # SKU MTL_SYSTEM_ITEMS_INTERFACE


Materialogic Description SKU Description MTL_SYSTEM_ITEMS_INTERFACE


Materialogic Unit Unit of measure code MTL_SYSTEM_ITEMS_INTERFACE

PRIMARY_UOM_CODE

If not supplied, will need to be entered into the extract file to be converted

Materialogic Wrap Wrap Code MTL_SYSTEM_ITEMS_INTERFACE NA Not needed for conversion

Materialogic U/Wrap Number of units in the wrap


ATTRIBUTE3

Materialogic Clas Monthly Qty restirction NA NA Not needed for conversion

Materialogic Cycle Order Qty restirction NA NA Not needed for conversion

Materialogic Interm NA NA Not needed for conversion

Materialogic Sesn Seasonal Code MTL_SYSTEM_ITEMS_INTERFACE NA Not needed for conversion

Materialogic Secur Secure Code MTL_SYSTEM_ITEMS_INTERFACE NA Not needed for conversion

Materialogic R/O Reorderable MTL_SYSTEM_ITEMS_INTERFACE

INVENTORY_ITEM_STATUS_CODE

Materialogic Lead Lead Time MTL_SYSTEM_ITEMS_INTERFACE

CUMULATIVE_TOTAL_LEAD_TIME

Materialogic Fixed Reorder Point Value MTL_SYSTEM_ITEMS_INTERFACE NA Not needed for conversion

Materialogic ChgcdCharge code determining “Free” or “Charge”

MTL_SYSTEM_ITEMS_INTERFACE NA Not needed for conversion



Position:From

Position:To


Materialogic Serial Serial Number NA NA Not needed for conversion

Materialogic Critcl Indicates critical nature of order

MTL_SYSTEM_ITEMS_INTERFACE NA Not needed for conversion

Materialogic B/O Cd Backorderable MTL_SYSTEM_ITEMS_INTERFACE

BACK_ORDERABLE_FLAG

Materialogic Abs Asolute Maximum MTL_SYSTEM_ITEMS_INTERFACE NA Not needed for conversion

Materialogic Acct Account Code for categorizing inventory

MTL_SYSTEM_ITEMS_INTERFACE TBD

Materialogic Summ MTL_SYSTEM_ITEMS_INTERFACE TBD

Materialogic Lolev Contact for Low Stock MTL_SYSTEM_ITEMS_INTERFACE

BUYER_ID Buyer identifier

Materialogic Productn NA NA Not needed for conversion

Materialogic Compnion NA NA Not needed for conversion

Materialogic Major NA NA Not needed for conversion

Materialogic Sub NA NA Not needed for conversion

Materialogic Sector NA NA Not needed for conversion

Materialogic Vend1 NA NA Not needed for conversion

Materialogic OutVnd NA NA Not needed for conversion


WEIGHT_UOM_CODE

Weight unit of measure


x xMTL_SYSTEM_ITEMS_INTERFACE ATTRIBUTE1

TARGET SYSTEM IDENTIFICATION will need to be entred into the extract file using the list of values


Extract File layout – Oracle 11.5.8 (mtl_system_items)

Datafile Oracle Table Oracle Column Datatype

Position:From

Position:To


Oracle 11.5.8 MTL_SYSTEM_ITEMS_B SEGMENT1 MTL_SYSTEM_ITEMS_INTERFACE SEGMENT1 Key Flexfield segment

Oracle 11.5.8 MTL_SYSTEM_ITEMS_B DESCRIPTION MTL_SYSTEM_ITEMS_INTERFACE DESCRIPTION Item description

Oracle 11.5.8 MTL_SYSTEM_ITEMS_B PRIMARY_UNIT_OF_MEASURE

MTL_SYSTEM_ITEMS_INTERFACE PRIMARY_UNIT_OF_MEASURE


Oracle 11.5.8 MTL_SYSTEM_ITEMS_B WEIGHT_UOM_CODE MTL_SYSTEM_ITEMS_INTERFACE WEIGHT_UOM_CODE Weight unit of measure



Descriptive Flexfields

Product Catalog Type and Meeting Fee Catalog Type

Application Title

Oracle Inventory Items

Number

Name Window Prompt Column Values Set Required

Displayed

Enabled

TBD CATALOG_TYPE CATALOG TYPE ATTRIBUTE13 INV_CATALOG_TYPE N Y Y

This DFF will be OPTIONAL

This DFF will be controlled at the organization level

There will be no default value

Value set will include

Value Description

100 DAY PACKS

101 SCALES

102 MONTHLY MAGAZINES

103 SPECIAL EDITION MAGAZINES

105 MISCELLANEOUS

107 COOKBOOKS

108 PROGRAM TOOLS

109 VITAMINS

110 FREE GIFTS

111 FOOD PRODUCTS

112 TAPES

113 VITAMINS

114 EXERCISE EQUIPMENT

115 COOKING UTENSILS – BOUTIQUE

116 APPLIANCE – BOUTIQUE

117 MISC PCKGE GOODS – BOUTIQUE

118 PERS. SECURITY – BOUTIQUE

119 APPAREL – BOUTIQUE

120 PROGRAM MATERIAL

121 CORPORATE SOLUTIONS

150 CHARITABLE PRODUCTS

160 ECOMMERCE BARS

161 ECOMMERCE CANDY

162 ECOMMERCE SMOOTHIES


163 ECOMMERCE SNACKS

164 ECOMMERCE FOOD

165 ECOMMERCE PROGRAM TOOLS

166 ECOMMERCE COOKBOOKS

167 ECOMMERCE EQUIPMENT

200 PREPAYMENT PLAN

250 PREPAID MEMBERS

290 SERVICE FEE

300 PAID LIFE (OBSELETE)

350 FREE LIFE

400 ENROLLMENTS

500 CURRENT MEMBERS

550 FREE CHOICE

700 MISSED MEETINGS

900 LECTURE INCOME (OBSELETE)

Champ Product Type

Application Title


Number


Displayed

Enabled

TBD CHAMP_PRODUCT_TYPE CHAMP PRODUCT TYPE ATTRIBUTE13 INV_CHAMP_PRODUCT_TYPE

N Y Y

This DFF will be OPTIONAL



Value set will include:

Value Description

99 STARTER KIT

100 BARS

101 SMOOTHIES/FRUITIES/MIX-INS

102 COMPANION BOOKS103 PROGRAM TOOLS

104 ORGANIZER

105 MAGAZINES

106 BOOKS107 FOOD SCALES

108 EXERCISE EQUIP AND WATER CONTAINER

109 COOKING UTENSILS AND AIDS


110 MISCELLANEOUS ITEMS

111 DISCONTINUED ITEMS

Target System Identification

Application Title


Number


Displayed

Enabled

TBD TARGET_SYSTEM_IDENTIFICATION

TARGET SYSTEM IDENTIFICATION

ATTRIBUTE1 INV_TARGET_SYS_ID Y Y Y

This DFF will be REQUIRED



Value set will include

Value Description

001 APOLLO

002 INFOPLUS

003 ECOMMERCE

001, 002 APOLLO AND INFOPLUS

001, 003 APOLLO AND ECOMMERCE

002, 003 INFOPLUS AND ECOMMERCE

001, 002, 003 APOLLO INFOPLUS AND ECOMMERCE


Data Clean-up

Below is a description of the data clean-up efforts required for the conversion of the Item Master:

No duplicate items

No obsolete items

No inactive items

Decide on standards for the item description, i.e. all upper case or mixed case


Data Validation

Below is a description of the data validation that should occur for the conversion of Item Master:

The number of rows on the extract must match the number of rows converted

The number of rows converted must match the number of items in the Item Master

Query random items, or items by item type to ensure that the correct template was applied and attributes were assigned correctly.


Open and Closed Issues for this Deliverable

Open Issues

ID Issue Resolution Responsibility Target Date Impact Date

confirm

9

OVERHEAD COSTSIf overhead rates can be standardize across items for InfoPlus, we can disregard the cost conversion effort in this document and use standard functionality to default rates by overhead subelements for all items.

CONFIRM:

OVERHEAD COSTS will default.

SUBELEMENTS need to be determined and a value for each

MARIA DEMATOS & MIKE BEALL

Closed Issues


1

PC_SHORT_DESCCheck to see if this description is used anywhere in I legacy reports/ invoices etc, as we can use the description from PC_DESC instead of both

No longer required PC_DESC will be used instead

ROBIN DOXEY

2

PSD_NOProduct storage definitionCan we use categories to satisfy this mapping

Not required for conversion. Categories will contain this information going forward

I

3

pcd_noProduct definitionCan we use categories to satisfy this mapping


See DFF section for values.


ROBIN DOXEY

4 pcit_noProduct typeCan we use

not required for conversion. categories will

ROBIN DOXEY



categories to satisfy this mapping

contain this information going forward. however, this will be required for the item interface from oracle to Apollo and a value of “2” will be hard coded into the interface. this will not be maintained in Oracle.

5

Additions to APOLLO PRODUCT extract:Primary UOMTemplate NameWeight UOM

These need to be added to the extract file prior to loading into the Oracle staging table

I

6

Additions to APOLLO MEETING extract:Primary UOMTemplate Name

These need to be added to the extract file prior to loading into the Oracle staging table

I

8

DFF required for APOLLO (ppt_no). List of values will be required for setup.

See DFF section for values.

Only active values (above) will be setup

ROBIN DOXEY

10

Confirm that PC_UNIT_PRICE is in fact the Material Cost for an item

This is the unloaded cost of goods and will be handled as part of Onhand balance and Cost CNV007

JIM GETTY

11Flag for member fees will not be maintained in Oracle

These flags will only be maintained in Apollo.Categories will be used for categorizing in oracle

ROBIN DOXEY


item conversion

Documents