Serial Numbers

Tables Involved:

MTL_SERIAL_NUMBERS

• SERIAL_NUMBER
• CURRENT_STATUS

Status Codes (ID and Meaning) 
--------------------- 
1 Defined but not used 
3 Resides in stores 
4 Issued out of stores 
5 Resides in intransit 
6 Pending status 
7 Resides in receiving 
8 Resides in WIP 

• GROUP_MARK_ID, LINE_MARK_ID, LOT_LINE_MARK_ID

* When the mark ids are populated, other transactions cannot use the serials.

MTL_UNIT_TRANSACTIONS

MTL_SERIAL_NUMBERS_TEMP

MTL_SERIAL_NUMBERS_INTERFACE

Queries:

SELECT SERIAL_NUMBER_CONTROL_CODE 
   FROM apps.MTL_SYSTEM_ITEMS_B
WHERE Inventory_Item_ID = ;

Control Codes (ID and Meaning) 
--------------------- 
1 No serial number control 
2 Predefined serial numbers 
5 Dynamic entry at inventory receipt 
6 Dynamic entry at sales order issue

Oracle Install Base

Oracle Install Base (IB) is an item instance life cycle tracking application.

Mark item as Install Base Trackable in Oracle Inventory and when item instance is created, it's created in IB (CSI_ITEM_INSTANCES) and further changes traced (CSI_TRANSACTIONS, CSI_ITEM_INSTANCES_H). Traced from the time it's received in inventory.

Usually serialized items, When a trackable item is defined in Oracle Inventory as non-serialized, it is typically for smaller objects (can be tracked only as a group) whose real-world instances do not require individual tracking e.g. screw.

Items can be serialized at receipt or at sales order issue.

Oracle Install Base interacts with: 

• Oracle PO (PO receipt --> Update Inv Data --> IB created or changed), 
• Oracle WIP (Issue components --> Complete work order --> Update Inv Data --> IB created or changed),
• Oracle OM (),

To find the Installation Details for a Sales Order number:

select ctld.*

from csi_t_transaction_lines ctl,

csi_t_txn_line_details ctld

where ctl.transaction_line_id = ctld.transaction_line_id (+)

and ctl.source_transaction_id in

(select line_id from oe_order_lines_all where header_id=

(select header_id from oe_order_headers_all

where order_number = ))

Period End Close - Inventory

Uncosted Material Transactions Issues (Costed_Flag = 'E'):
SELECT 'UNCOSTED TXNS',
organization_id,
transaction_id,
TO_CHAR(creation_date,'DD-MON-YY HH24:MI:SS') creation_date,
request_id,
transaction_set_id,
transaction_group_id,
costed_flag, error_code, error_explanation, mmt.*
FROM mtl_material_transactions mmt
WHERE costed_flag IN ('E','N');
1. Error Code: Error in obtaining the transfer price
Resolution: Add the item to the source price list and re-process (by Material cost transaction worker).
_______________________
_______________________
Source: ORDER ENTRY
The stuck transaction can be viewed by using the below query:
SELECT * FROM Mtl_Transactions_Interface WHERE Lock_Flag = 1
2. Error Code: 100
Error Explanation: failure:Ele_Acct( 40 ): 100 Error populating code combination segments
Resolution:

Collaborative Planning

Oracle Collaborative Planning (CP) is part of Advanced Planning Suite (APS), just like ASCP, Demantra etc.

Helps you collaborate securely with your trading partners, reduce planning cycle time, and identify and react quickly to supply chain exceptions. Oracle Collaborative Planning enables you to increase forecast accuracy, implement vendor managed and consigned inventory processes, increase global supply visibility, reduce supply shortages, and lower expediting cost.

Vendor Managed Inventory (VMI)

Vendor Managed Inventory (VMI) is a streamlined approach to inventory management and order fulfillment. Instead of sending purchase orders, customers electronically send daily demand information to the supplier. The supplier generates replenishment orders for the customer based on this demand information. The process is guided by mutually agreed upon objectives for the customer's inventory levels, fill rates, and transaction costs.

In the transformation from a 'push' approach to supply chain management to a demand-driven 'pull' approach, unfiltered demand signals are a critical element. VMI provides those signals.

Vendor Managed Inventory with suppliers (supplier-facing VMI) enables you to share on-hand, supplies, and forecast information with suppliers, transfer replenishment responsibility to suppliers, and automate replenishment processes.

VMI

Note that suppliers who enter into VMI recognize the need to maintain a high level of trust with their customers - a transparent system makes it clear to both parties if performance criteria is not being met.

Period End Close - Inventory

Once closed, Inventory period can't be re-opened from Apps. Execute below to re-open a closed inventory period:

SELECT acct_period_id period, open_flag, period_name name,  
      period_start_date, schedule_close_date, period_close_date  
      FROM  org_acct_periods  
      WHERE organization_id = &org_id  
      order by 1,2;
UPDATE org_acct_periods 
SET open_flag = 'Y', 
period_close_date = NULL, 
summarized_flag = 'N' 
WHERE organization_id = &&org_id 
AND acct_period_id >= &&acct_period_id; 
DELETE mtl_period_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 
DELETE mtl_period_cg_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 
DELETE mtl_per_close_dtls 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 
DELETE cst_period_close_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id;  
commit

Tables Info - Cost Management

CST_ITEM_COSTS

• Inventory_Item_ID
• Cost_Type_ID
• Organization_ID
• Item_Cost

CST_ITEM_COST_DETAILS

• Inventory_Item_ID
• Cost_Type_ID
• Cost_Element_ID
• Organization_ID
• Item_Cost

Period End Close - General

Periods represent non-overlapping consecutive date ranges. Every Financials customer needs to successfully close the accounting books each period.
General Steps
Here are some guidelines, from a general perspective, how you can perform the close process for every subledger:
a) Complete transactions
b) Create accounting
c) Reconcile transactions
d) Post to General Ledger
e) Reconcile accounting
f) Close period

Step b), Create Accounting, is a new step in Release 12, as it is an SLA feature. The accounting can be created immediately online, or can be run as a process in the background e.g. as a batch job at a time that matches your business needs.
The important thing to note, is that the accounting is not the distribution on the transaction. The final accounting by SLA could result in different accounting entries being generated based on the accounting rules you have configured. Therefore, you need to ensure that all your accounting for your transactions is created before you post to General Ledger, in order to update the balances correctly.

In Release 11i, when you close your periods in each of the subledgers, Oracle General Ledger will automatically create the balancing lines for journals posted to General Ledger. This changes in Release 12 as subledger accounting creates the balancing lines at the time you account for the subledger transactions.

The subledger accounting journal represents your REAL accounting and the balancing lines are created as part of these subledger accounting journals. When you close a period in Release 12, you’re actually closing subledger accounting. The journals can then be posted to the general ledger to update the GL balances.

Period End Close Dependencies

Interfaces

Inventory

• Item Import
  
  
  
  
  

Legacy System --> Mtl_System_Items_Interface --> Item Import Program --> Mtl_System_Items_B (or Mtl_Interface_Errors)

• Material Transactions

Interface Tables to be used are:
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

Transaction Type ID should be 2 (for "Subinventory Transfers") and "Material Transactions Manager" will process the records from the Interface tables.

If there are any errors while processing, the details of the errors will be available in MTL_INTERFACE_ERRORS table.

Cost Management

• Item Cost Import

Sample insert statement to populate the Item Cost Interface table

INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(INVENTORY_ITEM_ID
,ORGANIZATION_ID
,COST_TYPE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,Process_Flag
,Item_Cost
,Cost_Element_ID
,USAGE_RATE_OR_AMOUNT
)
SELECT INVENTORY_ITEM_ID
,ORGANIZATION_ID
,COST_TYPE_ID
,SYSDATE
,1441
,SYSDATE
,1441
,14411441
,1
,5
,1
,5
from cst_item_costs where inventory_item_id = 13433714 --12456335
AND Cost_Type_ID = 3

Payables

• Invoice Import

Interface Tables
AP_INVOICE _INTERFACE is the header info
AP_INVOICES_LINES_INTERFACE is the lines table.

RUN PAYABLES INVOICE IMPORT CONCURRENT PROGRAM

Base Tables
AP_INVOICE_ALL is also the header information is stored.
AP_INVOICES_DISTRIBUTIONS_ALL is the lines information table.
AP_PAYMENTS_SCHEDULES_ALL (AMOUNT_REMAINING stores balance amount to be paid)
AP_INVOICE_PAYMENTS_ALL (INVOICE_ID)
AP_CHECKS_ALL (CHECK_ID)

AP_INTERFACE_REJECTIONS (REJECT_LOOKUP_CODE stores the error occurred during the invoice import ex: account required, invalid supplier site )

Tables Info - Inventory

MTL_SYSTEM_ITEM_B - Inventory item definitions

• Inventory_Item_ID - Item ID
• Segment1 - Item Name
• Costing_Enabled_Flag*

*There is a trigger ‘Mtl_System_Items_T1’ on this table which updates Cst_Item_Costs table in case of any update to ‘Costing_Enabled_Flag’

MTL_SYSTEM_ITEMS_TL - Translations table table holding item descriptions in multiple languages

MTL_SYSTEM_ITEMS_INTERFACE - Inventory Items Open Interface table

MTL_PARAMETERS - Inventory control options and defaults

• Organization_ID
• Organization_Code
• Master_Organization_ID

MTL_SECONDARY_INVENTORIES - Subinventory definitions

MTL_CATEGORIES_B - Code combinations table for item categories

MTL_ITEM_CATEGORIES - Define item assignments to categories

• Inventory_Item_ID
• Organization_ID
• Category_Set_ID
• Category_ID

MTL_CATEGORY_SETS_B - Category Sets

• Category_Set_ID
• Structure_ID
• Validate_Flag
• Control_Level
• Category_Set_Name
• Description
• Multi_Item_Cat_Assign_Flag

MTL_CATEGORY_SET_VALID_CATS - Valid categories for category sets

MTL_ITEM_CATALOG_GROUPS_B - Item catalog groups; it's the code combinations table for item catalog groups.
MTL_MATERIAL_TRANSACTIONS - Material transaction table
MTL_MATERIAL_TRANSACTIONS_TEMP - Temporary table for processing material transactions

MTL_TRANSACTIONS_INTERFACE - Gateway for externally generated material transactions
MTL_TRANSACTION_REASONS Inventory Transaction Reasons Table
MTL_TRANSACTION_TYPES Inventory Transaction Types Table
MTL_TRANSACTION_LOTS_INTERFACE Temporary lot storage for transaction processor
MTL_TRANSACTION_LOTS_TEMP Temporary lot numbers holding table
MTL_TRANSACTION_LOT_NUMBERS Transaction lot numbers MTL_CROSS_REFERENCES Assign cross references to items MTL_ABC_ASSGN_GROUP_CLASSES Assigns abc classes to abc groups
MTL_ABC_ASSIGNMENTS Assigns items to abc classes in an abc group
MTL_ABC_ASSIGNMENT_GROUPS Defines abc groups
MTL_ABC_CLASSES Inventory ABC classes
MTL_ABC_COMPILES ABC descending value compile data
MTL_ABC_COMPILES_EFC Euro as a Functional Currency Archive
MTL_ABC_COMPILE_HEADERS ABC compile header data
MTL_ABC_COMPILE_HEADERS_EFC Euro as a Functional Currency Archive
MTL_ACTUAL_COST_SUBELEMENT Stores the material overhead subelements
MTL_ACTUAL_COST_SUBELEMENT_EFC Euro as a Functional Currency Archive
MTL_ATP_RULES ATP computation rules
MTL_ATTR_APPL_DEPENDENCIES Dependencies between item attributes and Oracle products
MTL_BIS_INV_BY_PERIOD BIS table that stores inventory turns data by organization,
accounting period and Inventory item id for open periods and closed periods
MTL_CATALOG_SEARCH_ITEMS Items returned by item searches
MTL_CATEGORIES_TL Categories table holding translated Description column for Item Categories
MTL_CATEGORY_ACCOUNTS Asset and expense accounts by item category, by subinventory MTL_CATEGORY_SETS_TL A table holding translated Name and Description columns
for Category Sets
MTL_CC_ENTRIES_INTERFACE Cycle Count Entries Interface data
MTL_CC_INTERFACE_ERRORS Cycle Count Entries Interface process errors
MTL_CC_SCHEDULE_REQUESTS Defines cycle count schedule requests
MTL_CC_SCHED_COUNT_XREFS Cross references count entry requests
MTL_CC_SERIAL_NUMBERS Serial Numbers for Cycle Count Entries.
MTL_CC_SUBINVENTORIES Defines subinventories to be used in subinventory level cycle count
MTL_CI_INTERFACE Customer Items Interface
MTL_CI_XREFS_INTERFACE Customer Items Cross References Interface
MTL_COMMODITY_CODES Customer item commodity codes
MTL_COPY_ORG_INTERFACE Generate Inventory Organizations
MTL_COUNTRY_ASSIGNMENTS stores information concerning the assignment of countries
to economic zones.
MTL_CROSS_REFERENCES_INTERFACE (not currently used)
MTL_CROSS_REFERENCE_TYPES Define cross reference types
MTL_CST_ACTUAL_COST_DETAILS Actual cost information by element, by level
MTL_CST_ACTUAL_COST_DETAIL_EFC Euro as a Functional Currency Archive
MTL_CST_LAYER_ACT_COST_DETAILS Actual cost information by element, by level
MTL_CST_TXN_COST_DETAILS The cost of a transaction by element, by level
MTL_CST_TXN_COST_DETAILS_EFC Euro as a Functional Currency Archive
MTL_CUSTOMER_ITEMS Customer item Information
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items
MTL_CYCLE_COUNT_CLASSES Defines cycle count classes
MTL_CYCLE_COUNT_CLASSES_EFC Euro as a Functional Currency Archive
MTL_CYCLE_COUNT_ENTRIES Defines cycle count entries
MTL_CYCLE_COUNT_ENTRIES_EFC Euro as a Functional Currency Archive
MTL_CYCLE_COUNT_HEADERS Defines cycle count header information
MTL_CYCLE_COUNT_HEADERS_EFC Euro as a Functional Currency Archive
MTL_CYCLE_COUNT_ITEMS Defines items to be used in cycle count
MTL_DEFAULT_CATEGORY_SETS Default category set for a particular functional area
MTL_DEMAND Sales order demand and reservations
MTL_DEMAND_HISTORIES Sales order demand and reservations
MTL_DEMAND_INTERFACE Temporary demand storage for the transaction processor
MTL_DESCRIPTIVE_ELEMENTS Descriptive element definitions for an item catalog group
MTL_DESCR_ELEMENT_VALUES Stores the descriptive element values for a particular item
MTL_FORECAST_RULES Forecast rules for forecast processes
MTL_INTERCOMPANY_PARAMETERS Inter–company relationships
MTL_INTERFACE_ERRORS Item open interface errors
MTL_INTERFACE_PROC_CONTROLS I nterface Processes Control Parameters
MTL_INTERORG_PARAMETERS Inter–organization relationships
MTL_INTERORG_SHIP_METHODS Relationships between shipping networks and shipment methods
MTL_ITEM_ATTRIBUTES I tem attributes table
MTL_ITEM_ATTRIBUTES_TEMP Temporary Table specifically for item attributes copy form
MTL_ITEM_CATEGORIES_INTERFACE Item Category Open Interface table; used to load item assignments to category sets and categories into Oracle Inventory.
MTL_ITEM_LOCATIONS Definitions for stock locators
MTL_ITEM_LOC_DEFAULTS Inventory item receiving and shipping locator default values
MTL_ITEM_REVISIONS Item revisions
MTL_ITEM_REVISIONS_INTERFACE Item revisions interface
MTL_ITEM_STATUS Material status definitions
MTL_ITEM_SUB_DEFAULTS Inventory item receiving and shipping subinventory default values
MTL_ITEM_SUB_INVENTORIES Item–subinventory assignments
MTL_ITEM_TEMPLATES Item template definitions
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template
MTL_ITEM_VALUES_TEMP Holds Item Attributes and their values. Used in the Item Attributes Copyform.
MTL_KANBAN_CARDS Kanban Cards
MTL_KANBAN_CARD_ACTIVITY Kanban Card Activity
MTL_KANBAN_CARD_PRINT_TEMP Temporary table used to print kanban cards
MTL_KANBAN_PULL_SEQUENCES Kanban Pull Sequences
MTL_KANBAN_TEMPLATES
MTL_LOT_NUMBERS Lot number definitions
MTL_MANUFACTURERS Define manufacturers
MTL_MATERIAL_STATUSES Inventory status definition – Used by WMS only
MTL_MATERIAL_STATUSES_B Base table for inventory status definitions – Used by WMS
MTL_MATERIAL_TXN_ALLOCATIONS Unit allocations among repetitive schedules
MTL_MFG_PART_NUMBERS Define manufacturer part numbers
MTL_MGD_INVENTORY_POSITIONS Stores the data sets built for on line display or for export
MTL_MOVEMENT_PARAMETERS Movement parameter values
MTL_MOVEMENT_STATISTICS Stores all relevant information for movement statistics transactions.
MTL_ONHAND_QUANTITIES FIFO quantities by control level and receipt
MTL_ONHAND_QUANTITY_TEMP Temporary table for on–hand quantity queries
MTL_ORG_REPORT_TEMP Multi–organization inventory report temporary table
MTL_PAC_TXN_COST_DETAILS Transaction details for a period
MTL_PENDING_ITEM_STATUS History of item–status assignments
MTL_PHYSICAL_ADJUSTMENTS Physical inventory adjustments
MTL_PHYSICAL_INVENTORIES Physical inventory definitions
MTL_PHYSICAL_INVENTORY_TAGS Physical inventory tag definitions
MTL_PHYSICAL_SUBINVENTORIES Physical inventory specific subinventories
MTL_PICKING_RULES Picking Rule Definitions
MTL_PLANNERS Planner code definitions
MTL_PURGE_HEADER Transaction history purges
MTL_RELATED_ITEMS Item relationships
MTL_REPLENISH_HEADERS Replenishment header information
MTL_REPLENISH_HEADERS_INT Replenishment header interface information
MTL_REPLENISH_LINES Replenishment line information
MTL_REPLENISH_LINES_INT Replenishment line interface information
MTL_RESERVATIONS Reservations
MTL_RESERVATIONS_INTERFACE Reservations Interface
MTL_RTG_ITEM_REVISIONS Routing revisions
MTL_RTG_ITEM_REVS_INTERFACE Routing revisions interface table
MTL_SAFETY_STOCKS Safety stocks
MTL_SALES_ORDERS Local definitions of sales orders
MTL_SEASONALITY_INDICES Seasonality indices
MTL_SECONDARY_LOCATORS Item–subinventory–locator assignments
MTL_SERIAL_NUMBERS Serial number definitions
MTL_SERIAL_NUMBERS_INTERFACE Temporary serial number storage for transactions processor
MTL_SERIAL_NUMBERS_TEMP Temporary serial number holding table
MTL_SHORT_CHK_PARAM Shortage Parameters
MTL_STATUS_ATTRIBUTE_VALUES Item status code attribute values
MTL_SUMMARY_TEMP Transaction summaries
MTL_SUPPLY Stores incoming supply information
MTL_SUPPLY_DEMAND_TEMP Temporary table containing ATP details returned from ATP user–exit
MTL_TXN_COST_DET_INTERFACE Stores transaction cost by element and by level for
transactions to be imported through the transaction interface
MTL_TXN_REQUEST_HEADERS Move Order headers table
MTL_TXN_REQUEST_LINES Move order lines table
MTL_TXN_SOURCE_TYPES Valid transaction source types
MTL_UNITS_OF_MEASURE_TL Unit of measure definitions
MTL_UNIT_TRANSACTIONS Serial number transactions
MTL_UOM_CLASSES_TL Unit of measure classes
MTL_UOM_CLASS_CONVERSIONS Conversions between base units of two different classes
MTL_UOM_CONVERSIONS Unit of measure conversion table for both default and item specific conversions

Oracle Inventory

Move orders

are formal requests to move material within (one subinventory to another) the same inventory organization.

There are three types:

Requisition Move Orders - Manually crearted. User may choose to print a pick slip for the material handler to know where to get the material from.

Source Types --> subinventory transfer and account transfer

Replenishment Move Orders - pre-approved move orders that are created automatically from Min-Max Planning, Replenishment Counting, Kanban replenishment.

Source Type --> Subinventory

Pickwave Move Orders -  used in Order Management. The Oracle Shipping Execution pick release process generates a Pick Wave Move Order to move the material from its source location to the Staging subinventory.

Backend Story:

SELECT header_id, h.* FROM apps.MTL_TXN_REQUEST_HEADERS h

WHERE Request_Number IN ('16788419', '16874441');

SELECT line_status, l.* FROM apps.MTL_TXN_REQUEST_LINES l
WHERE header_id IN (16788751, 16870925); -- 5 is Closed
Select *from apps.mfg_lookups
where lookup_type LIKE 'MTL_TXN_REQUEST%'; --_STATUS';
SELECT * FROM apps.MTL_TXN_SOURCE_TYPES;