Oracle General Ledger
Gl_code_combinations
Setup > Accounts > Combinations
This table stores the valid account combinations.
The
value in your chart of account segments are stored in the columns
segment1 to segment30 depending on your application configuration.
For example, say your chart of accounts is
Company – Cost Centre – Account
then segment1 = company, segment 2 = cost centre and segment3 = account.
However, this sequencing of segments is not guaranteed therefore, its best to check your configuration.
Company – Cost Centre – Account
then segment1 = company, segment 2 = cost centre and segment3 = account.
However, this sequencing of segments is not guaranteed therefore, its best to check your configuration.
Another
important column is the account_type which signifies your account is an
Asset, Liability, Revenue, Expense or Owners Equity account.
Gl_je_batches
Journals > Enter
This table stores the journal entry batches. Journal entries are batched in General Ledger.
Some columns of interest includes :
- Name
- Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
- Status
- Default_period_name
- Posted_date
- Posting_run_id
Gl_je_headers
Journals > Enter
This table stores the journal entry headers. There is always two journal lines for each journal header.
Some columns of interest includes :
- Je_category
- Period_name
- Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
- Posted_flag
- Je_source
- Name
- Status
Gl_je_lines
Journals > Enter
This table stores the journal entry lines.
The
entered_dr and entered_cr stores the amount in the entered currency
whereas the accounted_dr and accounted_cr stores the amount in the
functional currency.
Other columns of interest includes :
- Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
- Period_name
- Status
- Description
- Reference_1..reference10 (these columns links back to your Subledgers)
For example, for Purchasing transactions
Reference_1 = ‘PO’
Reference_2 = po_headers_all.po_header_id
Reference_3 = po_distributions_all.po_distribution_id
Reference_4 = po_headers_all.segment? (this is the purchase order number)
Reference_2 = po_headers_all.po_header_id
Reference_3 = po_distributions_all.po_distribution_id
Reference_4 = po_headers_all.segment? (this is the purchase order number)
Oracle Payables
Ap_invoices_all
Invoices > Entry > Invoices
This
table stores all the invoices you enter. For an invoice to be approved,
the total invoice amount must be stored in ap_invoice_distributions_all
and ap_payment_schedules_all.
Some columns of interest includes :
- Invoice_num
- Invoice_date
- Amount_paid
- Invoice_currency_code
- Invoice_type_lookup_code
- Payment_status_flag
Ap_invoice_distributions_all
Invoices > Entry > Invoices
This
table stores the accounting information for the invoice you have
entered. There is one row for each invoice disribution, that is this
table corresponds to the Distributions window.
Some columns of interest includes :
- Line_type_lookup_code
- Dist_code_combination_id (credit entry)
- Accts_pay_code_combination_id (debit_entry)
- Base_amount (in functional currency)
Ap_checks_all
Payments > Entry > Payments
This table stores payments to suppliers.
Some columns of interest includes :
- Amount (in functional currency)
- Check_date
- Bank_account_name
- Check_number
- Payment_method_lookup_code
- Payment_type_flag
Ap_invoice_payments_all
Payments > Entry > Payments
This
table stores invoice payments to suppliers. This table is updated when
you confirm an automatic payment batch, enter a manual payment or
process a Quick Payment. Void payments are represented as a negative of
the original payment line.
Some columns of interest includes :
- Accounting_date
- Period_name
- Amount
- Payment_num
Ap_payment_distributions_all
Payments > Entry > Payments
This
table stores accounting information for payments. There is at least one
CASH payment distribution for each invoice payment. Additional rows may
include DISCOUNT, GAIN and LOSS distributions where appropriate.
Some columns of interest includes :
- Line_type_lookup_code (CASH/DISCOUNT/GAIN/LOSS)
- Base_amount
Oracle Purchasing
Po_vendors
Supply Base > Suppliers
This table stores supplier information.
Some columns of interest includes :
- Segment1 (supplier number)
- Vendor_name
- Terms_id
- Vendor_type
- Ship_to_location (link to hr_locations for location information)
- Bill_to_location (link to hr_locations for location information)
Po_vendor_sites_all
Supply Base > Suppliers
This table stores supplier sites information.
Some columns of interest includes :
- Pay_site_flag
- Purchasing_site_flag
- Address_line1 to address_line3
- City
- State
- Area_code
- Zip
Po_headers_all
Purchase Orders > Purchase Orders
This table stores the seven types of purchasing documents such as Purchase Order and Blanket Agreement.
Segment1 is the document number (i.e. purchase order number)
Some columns of interest includes :
- Agent_id (link to per_people_f for the buyer)
- Type_lookup_code
Po_lines_all
Purchase Orders > Purchase Orders
This table stores purchasing document lines.
Some columns of interest includes :
- Line_num
- Item_description
- Unit_price
- Unit_meas_lookup_code (unit of measure)
- Quantity
- Item_id (link to mtl_system_items for the item number)
- Category_id (link to mtl_categories for the category name)
Po_line_locations_all
Purchase Orders > Purchase Orders
This
table stores purchase order shipment schedules and blanket agreement
price breaks. A purchase order is closed when QUANTITY is equal to
QUANTITY_RECEIVED.
Some columns of interest includes :
- Quantity
- Quantity_accepted
- Quantity_received
- Quantity_cancelled
- Need_by_date
- Ship_to_organization_id (link to org_organization_definitions for the organization code)
Po_distributions_all
Purchase Orders > Purchase Orders
This
table stores the accounting information on a purchase order shipment.
This table is used for Standard and Planned Purchase Orders and Planned
and Blanket Purchase Order Release.
Some columns of interest includes :
- Quantity_ordered
- Quantity_billed
- Amount_billed
- Quantity_delivered
- Quantity_cancelled
- Destination_organization_id (link to org_organization_definitions for the organization code)
- Destination_subinventory
Rcv_shipment_headers
Receiving > Receipts
This
table stores the receiving information. The three receipt sources are
Supplier, Inventory and Internal Order. There is one receipt header per
receipt source.
Some columns of interest includes :
- Receipt_num
- Shipment_num
- Receipt_source_code
- Shipped_date
- Ship_to_org_id
Rcv_shipment_lines
Receiving > Receipts
This table stores information about items that have been shipped and/or received from a receipt source.
Some columns of interest includes :
- Line_num
- Quantity_shipped
- Unit_of_measure
- Item_id (link to mtl_system_items for item number)
- To_organization_id (link to org_organization_definitions for organization code)
- To_subinventory
- Shipment_line_status_code (EXPECTED, FULLY RECEIVED, PARTIALLY RECEIVED)
- Quantity_received
- Quantity_shipped
Oracle Inventory
Org_organization_definitions
Setup > Organizations > Parameters
This view contains basic information on all inventory organisations.
Some columns of interest includes :
- Organization_code
- Organization_name
- Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
- Inventory_enabled_flag
Mtl_secondary_inventories
Setup > Organizations > Subinventories
This table stores all subinventory information for an inventory organisation.
Some columns of interest includes :
- Secondary_inventory_name
- Description
Mtl_material_transactions
Transactions > Material Transactions (Inquiry)
This table stores all inventory transactions including cost updates.
Some columns of interest includes :
- Transaction_quantity
- Transaction_type_id
- Transaction_source_type_id
- Transaction_source_name
Mtl_transaction_accounts
Transactions > Material Distributions (Inquiry)
This
table stores the inventory accounting information. There are two rows
in this table for each transaction in mtl_material_transactions.
Some columns of interest includes :
- Transaction_date
- Gl_batch_id
- Accounting_line_type
- Base_transaction_value
Mtl_system_items
Items > Master Items or Items > Organization Items
This table stores the item definition. An item must exist in an inventory organisation.
Your
item number is stored in the columns segment1 to segment20 depending on
your application configuration. If you have configured your items to
have to segments then you may be using segment1 and segment2
Some columns of interest includes :
- Segment1 to segment20
- Description
- Invetory_item_flag
- Purchasing_item_flag
- Inventory_asset_flag
- Stock_enabled_flag
- Invoiceable_item_flag
- Shippable_item_flag
- So_transaction_flag
- Mtl_transactions_enabled_flag
- Primary_unit_of_measure
Mtl_onhand_quantities
On-hand, Availability > On-hand Quantities
This table stores quantity on hand in a location for each item.
Some columns of interest includes :
- Date_received
- Transaction_quantity
- Subinventory_code
Cst_item_costs
Costs > Item Costs
This
table stores the item cost information. Note that there can be multiple
costs per item and the actual cost is where the cost type is Frozen.
Some columns of interest includes :
- Cost_type_id (link to cst_cost_types)
- Item_cost
Oracle Receivables
Ra_customers
Customers > Standard
This table stores customer information.
Some columns of interest includes :
- Customer_name
- Customer_number
- Status
- Customer_prospect_code
- Customer_type
- Orig_system_reference (for imported customers from an external source)
Ra_addresses_all
Customers > Standard
This table stores customer address information and your remit-to addresses.
Some columns of interest includes :
- Status
- Orig_system_reference (for imported customer addresses from an external source)
- Address1 to address4
- City
- State
- Postal_code
Ra_site_uses_all
Customers > Standard
This
table stores the customer’s site and site purpose. You must have one
row for each address. A customer must have one bill to address for
Receivables. A customer must have one ship to address and one bill to
address for Order Entry.
Some columns of interest includes :
- Site_use_code (BILL_TO, SHIP_TO, STMTS, DUN/LEGAL)
- Primary_flag
- Status
- Location
Ra_customer_trx_all
Transactions > Transactions
This table stores invoice, debit memo, chargeback, commitment and credit memo header information.
Some columns of interest includes :
- Cust_trx_type_id (link to ra_cust_trx_types_all)
- Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
- Terms_id (link to ra_terms)
- Trx_number (invoice number)
- Trx_date (invoice date)
Ra_customer_trx_lines_all
Transactions > Transactions
This table stores the invoice, debit memo, chargeback, commitment and credit memo line information.
Some columns of interest includes :
- Line_number
- Description
- Quantity_ordered
- Quantity_credited
- Quantity_invoiced
- Unit_standard_price
- Unit_selling_price
- Line_type
- Extended_amount
- Revenue_amount
Ra_cust_trx_line_gl_dist_all
Transactions > Transactions
This
table stores the accounting information for revenue, unearned revenue,
unbilled receivables, receivables, charges, freight and tax for each
invoice or credit memo line.
Some columns of interest includes :
- Amount_gl_date
- Gl_posted_date
- Account_class (CHARGES/FREIGHT/TAX/REC/REV/UNBILL/UNEARN)
- Acctd_amount (functional currency)
Ar_cash_receipts
Receipts > Receipts
This table stores the payment information.
Some columns of interest includes :
- Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
- Status (APP, UNAPP, UNID, NSF, STOP, REV)
- Type (CASH, MISC)
- Receipt_number
- Amount
- Currency_code
- Pay_from_customer
- Receipt_date
Ar_receivable_applications
Receipts > Receipts
This table stores accounting entries for cash and credit memo applications.
Some columns of interest includes :
- Amount_applied
- Line_applied
- Tax_applied
- Application_type
- Display
- Gl_date
- Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Ar_payment_schedules
Transactions > Transactions and Receipts > Receipts
This
table stores all transactions except adjustments and miscellaneous cash
receipts. This table is updated when a transaction occurs against an
invoice, debit memo, chargeback, credit memo, on-account credit, or
receipt.
Some columns of interest includes :
- Amount_due_original
- Status
- Class (DEP, DM, PMT, GUAR, CM, CB, INV)
- Due_date
- Amount_due_remaining
- Invoice_currency_code
- Amount_applied
- Anmount_credited
- Amount_adjusted
