Inventory Tables

Oracle Inventory Tables

mtl_system_items
mtl_item_revisions
mtl_item_categories
mtl_material_transactions
mtl_transaction_lots_numbers
mtl_serial_numbers
mtl_system_item_interface
mtl_item_revisions_interface
mtl_item_categories_interface
mtl_transaction_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface

Order To cash Cycle-Techie reference

 

• Enter the Sales Order

Tables :oe_order_headers_all  -Header_id referencing key

• oe_order_lines_all
• wsh_delivery_details- delivery_id is the number
• wsh_delivery_assignments

*In shipping transaction form order status remains “Ready to Release”.

• Book the Sales Order

Data to be checked in :mtl_demand and mtl_reservations

• Launch Pick Release 

MTL_MATERIAL_TRANSACTIONS

• Ship Confirm

• MTL_MATERIAL_TRANSACTIONS_TEMP
• oe_order_lines_all
• MTL_MATERIAL_TRANSACTIONS
• mtl_transaction_accounts
• wsh_delivery_details
• wsh_delivery_assignments
• MTL_ONHAND_QUANTITIES

• Create Invoice .

       ra_customer_trx_all and ra_customer_trx_lines_all

RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.

RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

• Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
• Transfer to General Ledger
• Journal Import
• Posting

Procure to Pay cycle-For techies Reference

Procure to Pay cycle covers the following steps:

1. Creating Requisition:

This depends on internal good requirement.This will be raised by an employee only. 

• Inter Org  transfer-From one inventory to other inventory
• Raising Purchase order-No stock supplier should provide

Important tables:

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL.

Joins can be made using REQUISITION_HEADER_ID and REQUISITION_LINE_ID 

2. Creating Purchase Order

• PO_HEADERS_ALL
• PO_LINES_ALL
• PO_DISTRIBUTIONS_ALL (REQ_HEADER_REFERENCE_NUM in Distributions table is the Requisition number for this PO)
• PO_LINE_LOCATIONS_ALL

The join columns are

header_id and line_id 

3. Creating Receipt

Receipt Tables are:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES (Lines Table has PO_HEADER_ID)

4. Creating Invoice in AP
Once the goods is Recieved, We will pay the vendor.Raise an invoice in AP

Invoice Tables:

AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

Joins made using Invoice_id

Accounting Entries Tables:

AP_ACCOUNTING_EVENTS_ALL

AP_AE_HEADERS_ALL

AP_AE_LINES_ALL

Joins made using Accounting_event_id and ae_header_id

5. Paying the Invoice

Payment Tables:

AP_INVOICE_PAYMENTS_ALL

columns can be joined
invoice_id,accounting_event_id 

AP_PAYMENT_SCHEDULES_ALL:invoice_id

AP_CHECKS_ALL:doc_seq_id is the invoice number

AP_CHECK_FORMATS

AP_BANK_ACCOUNTS_ALL

AP_BANK_BRANCHES

AP_TERMS

6. Transfer, Import and Post to GL

Difference between org_id and organisation_id

Organization ID refers to a unique id of an inventory organization (mtl_parameters)

Org_id refers to a operating unit (hr_operating_units)

Interface Tables

GL INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ----------------------
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE

AP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES


TNAME TABTYPE

------------------------------ --------------------------------------
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE


RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE

FA INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- ----------------------


FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE

INVENTORY INTERFACE TABLES


TNAME TABTYPE


------------------------------ ------- ------------------------------------


MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE

TNAME TABTYPE


------------------------------ ------- --------------------------------------


MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE


TNAME TABTYPE


------------------------------ ------- ------------------------------------
MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE


PO INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- -------------------------


PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE

BOM INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- ----------------------------------


BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE

WIP INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- --------------------------


WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE

Lock a table

Locking a table prevents two users to update a table at same time. 

Using locks we can prevent data replications. 

LOCK TABLE employees
   IN EXCLUSIVE MODE;

This exclusive mode enables the user to look into the rows but can update it.

And this command for remote link tables.

LOCK TABLE employees@DB

IN SHARE MODE 

The alternative for this is to use sequence in your table.It will also prevent data duplications. 

Index

Indexing...


why we should go for indexing?


When we are using more then two tables with 'n' number of check with DB.
We can create index for the specific column name to be referenced.


e.g:


create

So now when i run the query in my package it will refer to the index in the ratio 1:1:1:1

i.e everything join conditionally.

This will reduce the time consumption also. index ix_index on per_all_people_f(person_id,effective_end_date,person_type_id,national_identifier);