Oracle SCM Complete Tables Reference

Procurement (PO)

PO_HEADERS_ALL (Purchase Order Header Table)

PO_HEADERS_ALL is one of the most important Oracle SCM Procurement tables. It stores Purchase Order header information such as PO Number, Supplier, Buyer, Currency, Approval Status, Dates, Terms, and overall document details.

Core Identifiers

Column	Description
PO_HEADER_ID	Primary key for Purchase Order Header
SEGMENT1	Purchase Order Number
TYPE_LOOKUP_CODE	Document Type (Standard, Blanket, Contract)
DOCUMENT_STATUS	Status of Purchase Order
REVISION_NUM	PO Revision Number
CURRENT_VERSION_ID	Current active version of PO

Supplier Information

Column	Description
VENDOR_ID	Supplier Identifier
VENDOR_SITE_ID	Supplier Site Identifier
VENDOR_CONTACT_ID	Supplier Contact
VENDOR_ORDER_NUM	Supplier PO Reference Number
EMAIL_ADDRESS	Supplier Email Address
FAX	Supplier Fax Number

Business Unit & Buyer Details

Column	Description
PRC_BU_ID	Procurement Business Unit
REQ_BU_ID	Requisition Business Unit
BILLTO_BU_ID	Bill-To Business Unit
SOLDTO_BU_ID	Sold-To Business Unit
SOLDTO_LE_ID	Sold-To Legal Entity
AGENT_ID	Buyer Identifier

Location & Shipping

Column	Description
SHIP_TO_LOCATION_ID	Ship-To Location
BILL_TO_LOCATION_ID	Bill-To Location
CARRIER_ID	Carrier / Transport Provider
MODE_OF_TRANSPORT	Transport Mode
SERVICE_LEVEL	Shipping Priority
SHIPPING_CONTROL	Transportation Responsibility

Financial & Currency Information

Column	Description
CURRENCY_CODE	PO Currency
RATE_TYPE	Currency Conversion Type
RATE_DATE	Currency Conversion Date
RATE	Conversion Rate
TERMS_ID	Payment Terms
BLANKET_TOTAL_AMOUNT	Total Blanket Agreement Amount
AMOUNT_RELEASED	Total Released Amount
AMOUNT_LIMIT	Maximum Release Amount
MIN_RELEASE_AMOUNT	Minimum Release Amount
FUNDS_STATUS	Budgetary Control Status

Approval & Status Tracking

Column	Description
APPROVED_FLAG	Approval Status Flag
APPROVED_DATE	Date of Approval
SUBMIT_DATE	Date Submitted for Approval
CANCEL_FLAG	Cancellation Flag
CLOSED_DATE	PO Closed Date
FROZEN_FLAG	Frozen Document Indicator
PENDING_SIGNATURE_FLAG	Pending Signature Status
SIGNATURE_REQUIRED_FLAG	Signature Required Indicator

Document Dates

Column	Description
START_DATE	Effective Start Date
END_DATE	Effective End Date
CREATION_DATE	Record Creation Date
LAST_UPDATE_DATE	Last Updated Date
REVISED_DATE	Revision Date
LAST_RELEASE_DATE	Last Release Date

Notes & Comments

Column	Description
COMMENTS	Internal Comments
NOTE_TO_VENDOR	Supplier Notes
NOTE_TO_RECEIVER	Receiver Notes
CHANGE_SUMMARY	Summary of Changes

Important Notes

Stores only Purchase Order Header information
Line-level data is stored in PO_LINES_ALL
Shipment schedules are stored in PO_LINE_LOCATIONS_ALL
Distributions are stored in PO_DISTRIBUTIONS_ALL
One PO Header can have multiple PO Lines
SEGMENT1 is the visible Purchase Order Number used in UI
Most Procurement reports start from this table

Common Joins

PO_LINES_ALL → PO_HEADER_ID
PO_LINE_LOCATIONS_ALL → PO_HEADER_ID
PO_DISTRIBUTIONS_ALL → PO_HEADER_ID
POZ_SUPPLIERS → VENDOR_ID
POZ_SUPPLIER_SITES_ALL_M → VENDOR_SITE_ID
HR_LOCATIONS_ALL → SHIP_TO_LOCATION_ID
AP_INVOICES_ALL → PO_HEADER_ID
RCV_TRANSACTIONS → PO_HEADER_ID

Important Flow


PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
PO_LINE_LOCATIONS_ALL
        ↓
PO_DISTRIBUTIONS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
AP_INVOICES_ALL

Example Query

SELECT
    pha.segment1 po_number,
    pha.document_status,
    pha.currency_code,
    pha.approved_date,
    pvs.vendor_name
FROM po_headers_all pha
LEFT JOIN poz_suppliers pvs
    ON pha.vendor_id = pvs.vendor_id
WHERE pha.document_status = 'OPEN';

PO_LINES_ALL (Purchase Order Lines Table)

PO_LINES_ALL stores line-level details of Purchase Orders in Oracle SCM. Each record represents an individual item or service line belonging to a Purchase Order. It contains item, quantity, pricing, category, taxation, receiving, and sourcing details.

Core Identifiers

Column	Description
PO_LINE_ID	Primary key for Purchase Order Line
PO_HEADER_ID	Reference to PO_HEADERS_ALL
LINE_NUM	Purchase Order Line Number
LINE_STATUS	Status of PO Line
LINE_TYPE_ID	Line Type Identifier

Item Information

Column	Description
ITEM_ID	Inventory Item Identifier
ITEM_DESCRIPTION	Description of Item or Service
ITEM_REVISION	Item Revision
CATEGORY_ID	Item Category
VENDOR_PRODUCT_NUM	Supplier Item Number
MANUFACTURER	Manufacturer Name
MANUFACTURER_PART_NUM	Manufacturer Part Number

Quantity & UOM

Column	Description
QUANTITY	Ordered Quantity
UOM_CODE	Unit of Measure
BASE_UOM	Base Unit of Measure
BASE_QTY	Base Quantity
SECONDARY_QUANTITY	Secondary Quantity
SECONDARY_UOM_CODE	Secondary Unit of Measure
SHIPPING_UOM_CODE	Shipping Unit of Measure
SHIPPING_UOM_QUANTITY	Shipping Quantity

Pricing Information

Column	Description
UNIT_PRICE	Unit Price
LIST_PRICE_PER_UNIT	List Price
BASE_UNIT_PRICE	Base Unit Price
TAX_EXCLUSIVE_PRICE	Price Excluding Tax
LIST_PRICE	Original Item Price
MARKET_PRICE	Market Price
AMOUNT	Line Amount
DISCOUNT_TYPE	Discount Type
DISCOUNT	Discount Value
DISCOUNT_REASON	Reason for Discount

Agreement & Blanket Details

Column	Description
QUANTITY_COMMITTED	Committed Quantity
COMMITTED_AMOUNT	Committed Amount
AMOUNT_RELEASED	Released Amount
MIN_RELEASE_AMOUNT	Minimum Release Amount
NOT_TO_EXCEED_PRICE	Maximum Allowed Price
ALLOW_PRICE_OVERRIDE_FLAG	Price Override Allowed
PRICE_TYPE_LOOKUP_CODE	Price Type
PRICE_BREAK_LOOKUP_CODE	Price Break Type

Tax & Financial Details

Column	Description
TAXABLE_FLAG	Taxable Indicator
TAX_NAME	Tax Name
TAX_CODE_ID	Tax Code Identifier
FUNDS_STATUS	Budgetary Control Status
CAPITAL_EXPENSE_FLAG	Capital Expense Indicator
TYPE_1099	1099 Type

Receiving & Tolerance

Column	Description
QTY_RCV_TOLERANCE	Receiving Tolerance Percentage
OVER_TOLERANCE_ERROR_FLAG	Reject Over Tolerance Receipts
UNORDERED_FLAG	Created from Unordered Receipt
CONSIGNMENT_LINE_FLAG	Consignment Indicator
AGING_PERIOD_DAYS	Consignment Aging Days

Status & Control

Column	Description
CANCEL_FLAG	Cancellation Indicator
CANCEL_DATE	Cancellation Date
CANCEL_REASON	Cancellation Reason
CLOSED_DATE	Line Closed Date
CLOSED_REASON	Reason for Closure
FIRM_DATE	Firm Date
FIRM_STATUS_LOOKUP_CODE	Firm Status
CREDIT_FLAG	Credit Line Indicator

Sourcing & Reference Information

Column	Description
FROM_HEADER_ID	Source PO Header
FROM_LINE_ID	Source PO Line
FROM_LINE_LOCATION_ID	Source Shipment Line
CONTRACT_ID	Contract Reference
AUCTION_HEADER_ID	Sourcing Negotiation Identifier
BID_NUMBER	Supplier Bid Number
SUPPLIER_REF_NUMBER	Supplier Reference Number

Dates

Column	Description
LINE_START_DATE	Agreement Line Start Date
START_DATE	Assignment Start Date
EXPIRATION_DATE	Line Expiration Date
RETROACTIVE_DATE	Retroactive Pricing Date
CREATION_DATE	Record Creation Date
LAST_UPDATE_DATE	Last Updated Date

Important Notes

Each PO Header can have multiple PO Lines
Stores item/service level information
Shipment schedules are stored in PO_LINE_LOCATIONS_ALL
Accounting distributions are stored in PO_DISTRIBUTIONS_ALL
Receiving transactions connect through shipment lines
Pricing and quantity details mainly come from this table
One of the most used Procurement reporting tables

Common Joins

PO_HEADERS_ALL → PO_HEADER_ID
PO_LINE_LOCATIONS_ALL → PO_LINE_ID
PO_DISTRIBUTIONS_ALL → PO_LINE_ID
EGP_SYSTEM_ITEMS_B → ITEM_ID
EGP_CATEGORIES_B → CATEGORY_ID
PO_LINE_TYPES_B → LINE_TYPE_ID
RCV_TRANSACTIONS → PO_LINE_ID

Important Flow


PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
PO_LINE_LOCATIONS_ALL
        ↓
PO_DISTRIBUTIONS_ALL
        ↓
RCV_TRANSACTIONS

Example Query

SELECT
    pha.segment1 po_number,
    pla.line_num,
    pla.item_description,
    pla.quantity,
    pla.unit_price,
    pla.line_status
FROM po_headers_all pha
JOIN po_lines_all pla
    ON pha.po_header_id = pla.po_header_id
WHERE pla.cancel_flag IS NULL;

PO_LINE_LOCATIONS_ALL (Purchase Order Shipment Schedule Table)

PO_LINE_LOCATIONS_ALL stores shipment schedule information for Purchase Order lines. It contains delivery schedules, receiving details, shipment tracking, matching options, quantities received/billed, promised dates, and receiving controls.

Core Identifiers

Column	Description
LINE_LOCATION_ID	Primary Key for Shipment Schedule
PO_HEADER_ID	Reference to PO Header
PO_LINE_ID	Reference to PO Line
SHIPMENT_NUM	Shipment Schedule Number
SHIPMENT_TYPE	Shipment Type
SCHEDULE_STATUS	Shipment Schedule Status

Quantity Tracking

Column	Description
QUANTITY	Ordered Quantity
QUANTITY_RECEIVED	Total Quantity Received
QUANTITY_ACCEPTED	Total Quantity Accepted
QUANTITY_REJECTED	Total Quantity Rejected
QUANTITY_BILLED	Total Quantity Invoiced
QUANTITY_CANCELLED	Cancelled Quantity
QUANTITY_SHIPPED	Total Quantity Shipped
SECONDARY_QUANTITY	Secondary Quantity

Shipment & Delivery Dates

Column	Description
REQUESTED_SHIP_DATE	Requested Ship Date
PROMISED_SHIP_DATE	Supplier Promised Ship Date
NEED_BY_DATE	Requested Delivery Date
PROMISED_DATE	Supplier Promised Delivery Date
LAST_ACCEPT_DATE	Last Acceptable Receipt Date
ANTICIPATED_ARRIVAL_DATE	Expected Arrival Date
CREATION_DATE	Record Creation Date
LAST_UPDATE_DATE	Last Updated Date

Receiving Controls

Column	Description
RECEIPT_REQUIRED_FLAG	Receipt Required Before Invoice
INSPECTION_REQUIRED_FLAG	Inspection Required
QTY_RCV_TOLERANCE	Receiving Tolerance %
QTY_RCV_EXCEPTION_CODE	Over Receipt Action
DAYS_EARLY_RECEIPT_ALLOWED	Allowed Early Receipt Days
DAYS_LATE_RECEIPT_ALLOWED	Allowed Late Receipt Days
ALLOW_SUBSTITUTE_RECEIPTS_FLAG	Allow Substitute Items
RECEIVING_ROUTING_ID	Receiving Routing

Shipping & Location Information

Column	Description
SHIP_TO_LOCATION_ID	Ship-To Location
SHIP_TO_ORGANIZATION_ID	Ship-To Inventory Organization
CARRIER_ID	Carrier Identifier
MODE_OF_TRANSPORT	Transportation Mode
SERVICE_LEVEL	Transportation Priority
FOB_LOOKUP_CODE	FOB Terms
FREIGHT_TERMS_LOOKUP_CODE	Freight Terms

Invoice & Matching Information

Column	Description
MATCH_OPTION	Invoice Matching Option
MATCHING_BASIS	Matching Basis
INVOICE_CLOSE_TOLERANCE	Invoice Close Tolerance
RECEIVE_CLOSE_TOLERANCE	Receiving Close Tolerance
FINAL_MATCH_FLAG	Final Invoice Match Indicator
AMOUNT_BILLED	Total Amount Invoiced

Tax & Financial Details

Column	Description
TAXABLE_FLAG	Taxable Indicator
TAX_NAME	Tax Name
TAX_CODE_ID	Tax Code Identifier
CALCULATE_TAX_FLAG	Calculate Tax Flag
TAX_EXCLUSIVE_PRICE	Price Excluding Tax
FUNDS_STATUS	Budgetary Control Status
ENCUMBERED_FLAG	Encumbrance Indicator
ASSESSABLE_VALUE	Assessable Value

Status & Closure

Column	Description
CANCEL_FLAG	Cancellation Indicator
CANCEL_DATE	Cancellation Date
CANCEL_REASON	Cancellation Reason
CLOSED_DATE	Closed Date
CLOSED_REASON	Reason for Closure
CLOSED_FOR_RECEIVING_DATE	Closed for Receiving
CLOSED_FOR_INVOICE_DATE	Closed for Invoice
SHIPMENT_CLOSED_DATE	Fully Closed Shipment Date

Important Notes

One PO Line can have multiple shipment schedules
Stores delivery and receiving schedule information
Acts as bridge between Procurement and Receiving
Receiving transactions mainly connect through LINE_LOCATION_ID
Invoice matching is heavily dependent on this table
Shipment schedules control receiving tolerances and delivery tracking

Primary Foreign Keys

Column	References Table
PO_HEADER_ID	PO_HEADERS_ALL
PO_LINE_ID	PO_LINES_ALL
FROM_HEADER_ID	PO_HEADERS_ALL
FROM_LINE_ID	PO_LINES_ALL
FROM_LINE_LOCATION_ID	PO_LINE_LOCATIONS_ALL
SOURCE_SHIPMENT_ID	PO_LINE_LOCATIONS_ALL
SHIP_TO_LOCATION_ID	HR_LOCATIONS_ALL
SHIP_TO_ORGANIZATION_ID	HR_ORGANIZATION_UNITS
TERMS_ID	AP_TERMS
RECEIVING_ROUTING_ID	RCV_ROUTING_HEADERS

Common Tables Used for Joins

Table	Join Condition
PO_HEADERS_ALL	PO_LINE_LOCATIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
PO_LINES_ALL	PO_LINE_LOCATIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
PO_DISTRIBUTIONS_ALL	PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID = PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID
RCV_TRANSACTIONS	PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID = RCV_TRANSACTIONS.PO_LINE_LOCATION_ID
RCV_SHIPMENT_LINES	PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID = RCV_SHIPMENT_LINES.PO_LINE_LOCATION_ID
AP_INVOICE_LINES_ALL	PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID = AP_INVOICE_LINES_ALL.PO_LINE_LOCATION_ID
HR_LOCATIONS_ALL	PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID = HR_LOCATIONS_ALL.LOCATION_ID
HR_ORGANIZATION_UNITS	PO_LINE_LOCATIONS_ALL.SHIP_TO_ORGANIZATION_ID = HR_ORGANIZATION_UNITS.ORGANIZATION_ID

Important Flow


PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
PO_LINE_LOCATIONS_ALL
        ↓
PO_DISTRIBUTIONS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
AP_INVOICE_LINES_ALL

Example Query

SELECT
    pha.segment1 po_number,
    pla.line_num,
    plla.shipment_num,
    plla.quantity,
    plla.quantity_received,
    plla.need_by_date
FROM po_headers_all pha
JOIN po_lines_all pla
    ON pha.po_header_id = pla.po_header_id
JOIN po_line_locations_all plla
    ON pla.po_line_id = plla.po_line_id
WHERE plla.cancel_flag IS NULL;

PO_DISTRIBUTIONS_ALL (Purchase Order Distribution Table)

PO_DISTRIBUTIONS_ALL stores accounting distribution details for Purchase Orders. It contains charge accounts, encumbrance details, accrual information, budgetary control data, destination inventory information, and accounting integration data.

Core Identifiers

Column	Description
PO_DISTRIBUTION_ID	Primary Key for PO Distribution
PO_HEADER_ID	Reference to PO Header
PO_LINE_ID	Reference to PO Line
LINE_LOCATION_ID	Reference to Shipment Schedule
DISTRIBUTION_NUM	Distribution Number
REQ_DISTRIBUTION_ID	Reference to Requisition Distribution

Accounting Information

Column	Description
CODE_COMBINATION_ID	Charge Account Combination
BUDGET_ACCOUNT_ID	Budget Account
ACCRUAL_ACCOUNT_ID	Accrual Account
VARIANCE_ACCOUNT_ID	Variance Account
DEST_CHARGE_ACCOUNT_ID	Destination Charge Account
DEST_VARIANCE_ACCOUNT_ID	Destination Variance Account
SET_OF_BOOKS_ID	Ledger / Set of Books

Quantity & Amount Tracking

Column	Description
QUANTITY_ORDERED	Ordered Quantity
QUANTITY_DELIVERED	Delivered Quantity
QUANTITY_BILLED	Billed Quantity
QUANTITY_CANCELLED	Cancelled Quantity
AMOUNT_ORDERED	Ordered Amount
AMOUNT_DELIVERED	Delivered Amount
AMOUNT_BILLED	Billed Amount
AMOUNT_CANCELLED	Cancelled Amount

Budgetary Control & Encumbrance

Column	Description
BUDGET_DATE	Budget Date
CANCEL_BUDGET_DATE	Cancel Budget Date
CLOSE_BUDGET_DATE	Close Budget Date
FUNDS_STATUS	Budgetary Control Status
ENCUMBERED_FLAG	Encumbrance Indicator
ENCUMBERED_AMOUNT	Encumbered Amount
UNENCUMBERED_AMOUNT	Unencumbered Amount
GL_ENCUMBERED_DATE	GL Encumbrance Date
GL_ENCUMBERED_PERIOD_NAME	GL Encumbrance Period

Accrual & Invoice Information

Column	Description
ACCRUED_FLAG	Accrued Indicator
ACCRUE_ON_RECEIPT_FLAG	Accrue on Receipt
AMOUNT_TO_ENCUMBER	Amount to Encumber
INVOICE_ADJUSTMENT_FLAG	Invoice Adjustment Flag
RECOVERABLE_TAX	Recoverable Tax Amount
NONRECOVERABLE_TAX	Nonrecoverable Tax Amount
TAX_EXCLUSIVE_AMOUNT	Amount Excluding Tax

Destination & Delivery Information

Column	Description
DESTINATION_TYPE_CODE	Destination Type
DESTINATION_ORGANIZATION_ID	Destination Organization
DESTINATION_SUBINVENTORY	Destination Subinventory
DELIVER_TO_LOCATION_ID	Deliver To Location
DELIVER_TO_PERSON_ID	Requester / Deliver To Person
DELIVER_TO_CUST_ID	Customer Identifier
DELIVER_TO_CUST_LOCATION_ID	Customer Delivery Location

Project & WIP Information

Column	Description
PJC_PROJECT_ID	Project Identifier
PJC_TASK_ID	Project Task Identifier
PJC_CONTRACT_ID	Project Contract Identifier
WIP_ENTITY_ID	WIP Job Identifier
WIP_OPERATION_SEQ_NUM	WIP Operation Sequence
WIP_RESOURCE_SEQ_NUM	WIP Resource Sequence
WIP_LINE_ID	WIP Line Identifier

Dates

Column	Description
CREATION_DATE	Record Creation Date
LAST_UPDATE_DATE	Last Updated Date
GL_CANCELLED_DATE	GL Cancellation Date
GL_CLOSED_DATE	GL Closed Date
RATE_DATE	Currency Conversion Date

Important Notes

Stores accounting distribution level data for Purchase Orders
One shipment schedule can have multiple accounting distributions
Main table used for Procurement Accounting and Budgetary Control
Links Procurement with GL, AP, Receiving, Inventory, and Projects
Used heavily in Accrual, Encumbrance, and Spend Analysis Reports
Contains both quantity and financial tracking information

Primary Foreign Keys

Column	References Table
PO_HEADER_ID	PO_HEADERS_ALL
PO_LINE_ID	PO_LINES_ALL
LINE_LOCATION_ID	PO_LINE_LOCATIONS_ALL
REQ_DISTRIBUTION_ID	PO_REQ_DISTRIBUTIONS_ALL
CODE_COMBINATION_ID	GL_CODE_COMBINATIONS
BUDGET_ACCOUNT_ID	GL_CODE_COMBINATIONS
ACCRUAL_ACCOUNT_ID	GL_CODE_COMBINATIONS
VARIANCE_ACCOUNT_ID	GL_CODE_COMBINATIONS
SET_OF_BOOKS_ID	GL_SETS_OF_BOOKS
DELIVER_TO_LOCATION_ID	HR_LOCATIONS_ALL
DELIVER_TO_PERSON_ID	HR_EMPLOYEES
DESTINATION_ORGANIZATION_ID	HR_ALL_ORGANIZATION_UNITS

Common Tables Used for Joins

Table	Join Condition
PO_HEADERS_ALL	PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
PO_LINES_ALL	PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
PO_LINE_LOCATIONS_ALL	PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID = PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
GL_CODE_COMBINATIONS	PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID = GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
AP_INVOICE_LINES_ALL	PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = AP_INVOICE_LINES_ALL.PO_DISTRIBUTION_ID
AP_INVOICE_DISTRIBUTIONS_ALL	PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
RCV_TRANSACTIONS	PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = RCV_TRANSACTIONS.PO_DISTRIBUTION_ID
INV_SUPPLY	PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = INV_SUPPLY.PO_DISTRIBUTION_ID

Important Flow


PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
PO_LINE_LOCATIONS_ALL
        ↓
PO_DISTRIBUTIONS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
AP_INVOICE_LINES_ALL
        ↓
GL_CODE_COMBINATIONS

Example Query

SELECT
    pha.segment1 po_number,
    pla.line_num,
    pda.distribution_num,
    gcc.concatenated_segments charge_account,
    pda.quantity_ordered,
    pda.amount_billed
FROM po_headers_all pha
JOIN po_lines_all pla
    ON pha.po_header_id = pla.po_header_id
JOIN po_distributions_all pda
    ON pla.po_line_id = pda.po_line_id
LEFT JOIN gl_code_combinations gcc
    ON pda.code_combination_id = gcc.code_combination_id;

POZ_SUPPLIERS (Supplier Master Table)

POZ_SUPPLIERS stores supplier master information in Oracle Fusion SCM. It contains supplier details such as supplier name, supplier number, supplier type, business relationship status, tax reporting information, and supplier control details.

Core Identifiers

Column	Description
VENDOR_ID	Primary Key for Supplier
SEGMENT1	Supplier Number
PARTY_ID	Reference to Trading Community Party
OBJECT_VERSION_NUMBER	Optimistic Lock Version Number
CREATION_SOURCE	Source of Supplier Creation

Supplier Details

Column	Description
VENDOR_TYPE_LOOKUP_CODE	Supplier Type
ORGANIZATION_TYPE_LOOKUP_CODE	Organization Type
ENABLED_FLAG	Supplier Active Status
SUMMARY_FLAG	Summary Supplier Indicator
REVIEW_TYPE	Supplier Review Type
SPEND_AUTHORIZATION_STATUS	Spend Authorization Status
SUPPLIER_LOCKED_FLAG	Supplier Locked Indicator

Tax & Financial Information

Column	Description
INCOME_TAX_ID_FLAG	Income Tax Identifier Available
NI_NUMBER_FLAG	National Insurance Number Indicator
FEDERAL_REPORTABLE_FLAG	Federal Reporting Indicator
TAX_REPORTING_NAME	Tax Reporting Name
ALLOW_AWT_FLAG	Allow Withholding Tax
AWT_GROUP_ID	Withholding Tax Group
BANK_CHARGE_BEARER	Bank Charge Bearer
BC_NOT_APPLICABLE_FLAG	Bank Charges Applicable Indicator

Integration & External References

Column	Description
EXTERNAL_SYSTEM_ID	External System Identifier
EXTERNAL_SYSTEM	External Source System
CHANGE_REQ_NUMBER	Change Request Number
REBUILD_INDEX	Supplier Index Rebuild Flag

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Supplier Master table in Oracle Fusion Procurement
Stores one record per supplier
Supplier Sites are stored in POZ_SUPPLIER_SITES_ALL_M
Supplier Contacts are stored separately
Used across Procurement, Payables, Sourcing, and Expenses
PARTY_ID links supplier to Trading Community Architecture (TCA)

Primary Foreign Keys

Column	References Table
PARTY_ID	HZ_PARTIES
AWT_GROUP_ID	AP_AWT_GROUPS

Common Tables Used for Joins

Table	Join Condition
PO_HEADERS_ALL	POZ_SUPPLIERS.VENDOR_ID = PO_HEADERS_ALL.VENDOR_ID
POZ_SUPPLIER_SITES_ALL_M	POZ_SUPPLIERS.VENDOR_ID = POZ_SUPPLIER_SITES_ALL_M.VENDOR_ID
AP_INVOICES_ALL	POZ_SUPPLIERS.VENDOR_ID = AP_INVOICES_ALL.VENDOR_ID
HZ_PARTIES	POZ_SUPPLIERS.PARTY_ID = HZ_PARTIES.PARTY_ID
POZ_SUPPLIER_CONTACTS	POZ_SUPPLIERS.VENDOR_ID = POZ_SUPPLIER_CONTACTS.VENDOR_ID

Important Flow


POZ_SUPPLIERS
        ↓
POZ_SUPPLIER_SITES_ALL_M
        ↓
PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
AP_INVOICES_ALL

Example Query

SELECT
    vendor_id,
    segment1 supplier_number,
    vendor_type_lookup_code supplier_type,
    organization_type_lookup_code organization_type,
    enabled_flag,
    spend_authorization_status
FROM poz_suppliers
WHERE enabled_flag = 'Y';

POZ_SUPPLIER_SITES_ALL_M (Supplier Sites Table)

POZ_SUPPLIER_SITES_ALL_M stores supplier site information in Oracle Fusion SCM. It contains procurement, payment, RFQ, communication, and location details for supplier sites associated with suppliers.

Core Identifiers

Column	Description
VENDOR_SITE_ID	Primary Key for Supplier Site
VENDOR_ID	Reference to Supplier
PARTY_SITE_ID	Reference to TCA Party Site
LOCATION_ID	Reference to Supplier Address Location
VENDOR_SITE_CODE	Supplier Site Code
PRC_BU_ID	Procurement Business Unit

Business & Site Details

Column	Description
CUSTOMER_NUM	Customer Number
OBJECT_VERSION_NUMBER	Optimistic Lock Version Number
OVERRIDE_B2B_COMM_CODE	B2B Communication Override
B2B_COMM_METHOD_CODE	B2B Communication Method
EFFECTIVE_START_DATE	Effective Start Date
EFFECTIVE_END_DATE	Effective End Date
EFFECTIVE_SEQUENCE	Effective Sequence
INACTIVE_DATE	Inactive Date

Site Usage Flags

Column	Description
PURCHASING_SITE_FLAG	Purchasing Site Indicator
RFQ_ONLY_SITE_FLAG	RFQ Only Site Indicator
PAY_SITE_FLAG	Pay Site Indicator
PRIMARY_PAY_SITE_FLAG	Primary Pay Site Indicator
PCARD_SITE_FLAG	PCard Site Indicator

Payment & Invoice Controls

Column	Description
MATCH_OPTION	Invoice Matching Option
CREATE_DEBIT_MEMO_FLAG	Create Debit Memo Indicator
GAPLESS_INV_NUM_FLAG	Gapless Invoice Numbering
RETAINAGE_RATE	Retainage Percentage
AUTO_CALCULATE_INTEREST_FLAG	Auto Interest Calculation
PAYMENT_HOLD_DATE	Payment Hold Date
HOLD_UNMATCHED_INVOICES_FLAG	Hold Unmatched Invoices

Hold & Control Information

Column	Description
HOLD_FLAG	Site Hold Indicator
HOLD_BY	Hold Applied By
HOLD_DATE	Hold Date
PURCHASING_HOLD_REASON	Purchasing Hold Reason
HOLD_ALL_PAYMENTS_FLAG	Hold All Payments
HOLD_FUTURE_PAYMENTS_FLAG	Hold Future Payments
HOLD_REASON	Payment Hold Reason
TAX_REPORTING_SITE_FLAG	Tax Reporting Site Indicator

Communication & Contact Details

Column	Description
SUPPLIER_NOTIF_METHOD	Supplier Notification Method
EMAIL_ADDRESS	Supplier Site Email Address
ATTENTION_AR_FLAG	Attention AR Indicator

Bank & Financial Information

Column	Description
BANK_CHARGE_BEARER	Bank Charge Bearer
BANK_CHARGE_DEDUCTION_TYPE	Bank Charge Deduction Type

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
PROGRAM_UPDATE_DATE	Program Update Date

Important Notes

Stores supplier site level details
One Supplier can have multiple Supplier Sites
Used in Procurement and Payables transactions
Controls invoice matching and payment behavior
Stores communication and purchasing controls
Main table used for Supplier Site reporting

Primary Foreign Keys

Column	References Table
VENDOR_ID	POZ_SUPPLIERS
PARTY_SITE_ID	HZ_PARTY_SITES
LOCATION_ID	HZ_LOCATIONS
PRC_BU_ID	FUN_ALL_BUSINESS_UNITS_V

Common Tables Used for Joins

Table	Join Condition
POZ_SUPPLIERS	POZ_SUPPLIER_SITES_ALL_M.VENDOR_ID = POZ_SUPPLIERS.VENDOR_ID
PO_HEADERS_ALL	POZ_SUPPLIER_SITES_ALL_M.VENDOR_SITE_ID = PO_HEADERS_ALL.VENDOR_SITE_ID
AP_INVOICES_ALL	POZ_SUPPLIER_SITES_ALL_M.VENDOR_SITE_ID = AP_INVOICES_ALL.VENDOR_SITE_ID
HZ_PARTY_SITES	POZ_SUPPLIER_SITES_ALL_M.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID
HZ_LOCATIONS	POZ_SUPPLIER_SITES_ALL_M.LOCATION_ID = HZ_LOCATIONS.LOCATION_ID

Important Flow


POZ_SUPPLIERS
        ↓
POZ_SUPPLIER_SITES_ALL_M
        ↓
PO_HEADERS_ALL
        ↓
AP_INVOICES_ALL

Example Query

SELECT
    pvs.vendor_site_code,
    pvs.purchasing_site_flag,
    pvs.pay_site_flag,
    pvs.email_address,
    pvs.match_option,
    pvs.hold_flag
FROM poz_supplier_sites_all_m pvs
WHERE pvs.inactive_date IS NULL;

POR_REQUISITION_HEADERS_ALL (Requisition Header Table)

POR_REQUISITION_HEADERS_ALL stores requisition header information in Oracle Fusion Procurement. It contains requisition number, preparer details, approval status, budgetary control information, emergency requisition data, and workflow tracking details.

Core Identifiers

Column	Description
REQUISITION_HEADER_ID	Primary Key for Requisition Header
REQUISITION_NUMBER	Requisition Number
PREPARER_ID	Employee who created the Requisition
REQ_BU_ID	Requisition Business Unit
PRC_BU_ID	Procurement Business Unit
SOLDTO_LE_ID	Sold To Legal Entity

Requisition Details

Column	Description
DESCRIPTION	Requisition Description
DOCUMENT_STATUS	Document Status
DOCUMENT_SUB_TYPE	Document Sub Type
JUSTIFICATION	Justification / Note to Approver
ACTIVE_REQUISITION_FLAG	Active Requisition Indicator
EXTERNALLY_MANAGED_FLAG	Externally Managed Indicator
INTERNAL_TRANSFER_REQ_FLAG	Internal Transfer Request Indicator
SOURCE_APPLICATION_CODE	Source Application

Approval & Workflow Information

Column	Description
SUBMISSION_DATE	Requisition Submission Date
APPROVED_DATE	Approval Date
APPROVAL_INSTANCE_ID	Approval Workflow Instance
APPROVAL_NOT_REQUIRED_FLAG	Approval Not Required Indicator
OVERRIDING_APPROVER_ID	Overriding Approver
MODIFYING_APPROVER_ID	Approver Modifying Requisition
REJECTED_BY_APPROVER_ID	Approver who Rejected Requisition
REJECT_REASON	Rejection Reason
REQ_IMPORT_APPROVER_ID	Imported Requisition Approver

Status Tracking Flags

Column	Description
HAS_ACTION_REQUIRED_LINES	Lines Require Action
HAS_WITHDRAWN_LINES	Contains Withdrawn Lines
HAS_CANCELED_LINES	Contains Cancelled Lines
HAS_RETURNED_LINES	Contains Returned Lines
HAS_REJECTED_LINES	Contains Rejected Lines
HAS_INCOMPLETE_LINES	Contains Incomplete Lines
HAS_PENDING_APPR_LINES	Contains Pending Approval Lines
LOCKED_BY_BUYER_FLAG	Locked by Buyer
CHANGE_PENDING_FLAG	Change Order Pending

Budgetary Control Information

Column	Description
FUNDS_STATUS	Budgetary Control Status
BUDGET_CONTROL_ENABLED_FLAG	Budgetary Control Enabled
FUNDS_CHK_FAIL_WARN_FLAG	Funds Check Failure/Warning
FUNDS_OVERRIDE_APPROVER_ID	Funds Override Approver
INSUFFICIENT_FUNDS_FLAG	Insufficient Funds Indicator

Emergency & Special Handling

Column	Description
EMERGENCY_REQ_FLAG	Emergency Requisition Indicator
EMERGENCY_PO_NUMBER	Reserved Emergency PO Number
PCARD_ID	Procurement Card Identifier
SPECIAL_HANDLING_TYPE_CODE	Special Handling Type
DEFAULT_TAXATION_COUNTRY	Default Taxation Country
TAX_USER_OVERRIDE_HDR_FLAG	User Override Tax Flag

Import & Integration Information

Column	Description
INTERFACE_SOURCE_CODE	Import Source
INTERFACE_SOURCE_LINE_ID	Source Line Identifier
PROCESS_STATUS	Import Process Status
LIFECYCLE_STATUS	Lifecycle Status

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Job Request ID
JOB_DEFINITION_NAME	ESS Job Name
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Important Notes

Main Requisition Header table in Oracle Fusion Procurement
Stores one record per Requisition
Line information is stored in POR_REQUISITION_LINES_ALL
Approval and budgetary control status are tracked here
Used heavily in Self Service Procurement reporting
Controls requisition workflow and approval routing

Primary Foreign Keys

Column	References Table
PREPARER_ID	PER_PERSONS / PER_ALL_PEOPLE_F
REQ_BU_ID	FUN_ALL_BUSINESS_UNITS_V
PRC_BU_ID	FUN_ALL_BUSINESS_UNITS_V
SOLDTO_LE_ID	XLE_ENTITY_PROFILES
PCARD_ID	IBY_CREDITCARDS

Common Tables Used for Joins

Table	Join Condition
POR_REQUISITION_LINES_ALL	POR_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID = POR_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID
PO_HEADERS_ALL	POR_REQUISITION_HEADERS_ALL.REQUISITION_NUMBER = PO_HEADERS_ALL.SEGMENT1
PER_ALL_PEOPLE_F	POR_REQUISITION_HEADERS_ALL.PREPARER_ID = PER_ALL_PEOPLE_F.PERSON_ID
FUN_ALL_BUSINESS_UNITS_V	POR_REQUISITION_HEADERS_ALL.REQ_BU_ID = FUN_ALL_BUSINESS_UNITS_V.BU_ID
POR_REQUISITION_DISTRIBUTIONS_ALL	POR_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID = POR_REQUISITION_DISTRIBUTIONS_ALL.REQUISITION_HEADER_ID

Important Flow


POR_REQUISITION_HEADERS_ALL
        ↓
POR_REQUISITION_LINES_ALL
        ↓
POR_REQUISITION_DISTRIBUTIONS_ALL
        ↓
PO_HEADERS_ALL
        ↓
PO_LINES_ALL

Example Query

SELECT
    prha.requisition_number,
    prha.document_status,
    prha.description,
    prha.submission_date,
    prha.approved_date,
    prha.funds_status
FROM por_requisition_headers_all prha
WHERE prha.document_status = 'APPROVED';

POR_REQUISITION_LINES_ALL (Requisition Lines Table)

POR_REQUISITION_LINES_ALL stores line-level requisition details in Oracle Fusion Procurement. It contains item, quantity, pricing, supplier, sourcing, receiving, inventory destination, tax, and requisition-to-PO conversion information.

Core Identifiers

Column	Description
REQUISITION_LINE_ID	Primary Key for Requisition Line
REQUISITION_HEADER_ID	Reference to Requisition Header
LINE_NUMBER	Requisition Line Number
LINE_TYPE_ID	Requisition Line Type
LINE_STATUS	Status of Requisition Line
LINE_GROUP	Line Group Classification

Item & Category Information

Column	Description
ITEM_ID	Inventory Item Identifier
ITEM_DESCRIPTION	Description of Item or Service
ITEM_REVISION	Item Revision
CATEGORY_ID	Item Category
UOM_CODE	Unit of Measure
ITEM_SOURCE	Item Source Type
UNSPSC_CODE	Commodity Classification Code
MANUFACTURER_NAME	Manufacturer Name
MANUFACTURER_PART_NUMBER	Manufacturer Part Number

Quantity & Pricing Information

Column	Description
QUANTITY	Requested Quantity
QUANTITY_DELIVERED	Delivered Quantity
QUANTITY_RECEIVED	Received Quantity
QUANTITY_CANCELLED	Cancelled Quantity
UNIT_PRICE	Unit Price
AMOUNT	Line Amount
CURRENCY_CODE	Currency Code
CURRENCY_UNIT_PRICE	Foreign Currency Unit Price
CURRENCY_AMOUNT	Foreign Currency Amount
RATE_TYPE	Currency Conversion Type
RATE_DATE	Currency Conversion Date
RATE	Currency Conversion Rate

Supplier Information

Column	Description
VENDOR_ID	Supplier Identifier
VENDOR_SITE_ID	Supplier Site Identifier
VENDOR_CONTACT_ID	Supplier Contact Identifier
SUGGESTED_VENDOR_NAME	Suggested Supplier Name
SUGGESTED_VENDOR_SITE	Suggested Supplier Site
SUGGESTED_VENDOR_CONTACT	Suggested Supplier Contact
SUGGESTED_SUPPLIER_ITEM_NUMBER	Supplier Item Number
SUPPLIER_REF_NUMBER	Supplier Reference Number
SUPPLIER_DUNS	Supplier DUNS Number

Buyer & Sourcing Information

Column	Description
SUGGESTED_BUYER_ID	Suggested Buyer
ASSIGNED_BUYER_ID	Assigned Buyer
NEGOTIATION_REQUIRED_FLAG	Negotiation Required
ON_RFQ_FLAG	Included in RFQ
AT_SOURCING_FLAG	Part of Active Sourcing
AUCTION_HEADER_ID	Sourcing Auction Identifier
AUCTION_DISPLAY_NUMBER	Sourcing Auction Number
BID_NUMBER	Supplier Bid Number
REQS_IN_POOL_FLAG	Available for PO Creation

PO & Transfer Order References

Column	Description
PO_HEADER_ID	Purchase Order Header Identifier
PO_LINE_ID	Purchase Order Line Identifier
LINE_LOCATION_ID	Shipment Schedule Identifier
TO_HEADER_ID	Transfer Order Header Identifier
TO_LINE_ID	Transfer Order Line Identifier
PARENT_REQ_LINE_ID	Parent Requisition Line
REINSTATE_LINE_LOCATION_ID	Reinstated Shipment Schedule

Destination & Receiving Information

Column	Description
DESTINATION_TYPE_CODE	Destination Type
DESTINATION_ORGANIZATION_ID	Destination Organization
DESTINATION_SUBINVENTORY	Destination Subinventory
DELIVER_TO_LOCATION_ID	Deliver To Location
REQUESTER_ID	Requester Identifier
NEED_BY_DATE	Required Delivery Date
REQUESTED_SHIP_DATE	Requested Ship Date
DAYS_EARLY_RECEIPT_ALLOWED	Allowed Early Receipt Days
DAYS_LATE_RECEIPT_ALLOWED	Allowed Late Receipt Days
QTY_RCV_TOLERANCE	Receiving Tolerance Percentage

Tax & Financial Information

Column	Description
DEFAULT_TAXATION_COUNTRY	Default Taxation Country
TAX_CLASSIFICATION_CODE	Tax Classification Code
PRODUCT_FISC_CLASSIFICATION	Product Fiscal Classification
ASSESSABLE_VALUE	Assessable Value
FUNDS_STATUS	Budgetary Control Status
PCARD_FLAG	PCard Purchase Indicator

Status & Control Flags

Column	Description
ACTION_REQUIRED_CODE	Required Action Code
URGENT_FLAG	Urgent Requisition Indicator
CANCEL_FLAG	Cancelled Indicator
CANCEL_DATE	Cancellation Date
CANCEL_REASON	Cancellation Reason
MODIFIED_BY_BUYER_FLAG	Modified by Buyer
BUYER_PROCESSING_FLAG	Buyer Processing Indicator
PRICE_VARIANCE_EXCEEDED_FLAG	Price Variance Exceeded
FIRM_FLAG	Firmed Requisition Indicator
LIFECYCLE_STATUS	Lifecycle Status
LIFECYCLE_SECONDARY_STATUS	Lifecycle Secondary Status

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Request Identifier
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Important Notes

Stores requisition line level information
One Requisition Header can contain multiple Requisition Lines
Main bridge between Requisition and Purchase Order
Tracks sourcing, receiving, supplier, and inventory details
Used heavily in Procurement lifecycle reporting
Supports catalog, non-catalog, punchout, and transfer requisitions

Primary Foreign Keys

Column	References Table
REQUISITION_HEADER_ID	POR_REQUISITION_HEADERS_ALL
PO_HEADER_ID	PO_HEADERS_ALL
PO_LINE_ID	PO_LINES_ALL
LINE_LOCATION_ID	PO_LINE_LOCATIONS_ALL
VENDOR_ID	POZ_SUPPLIERS
VENDOR_SITE_ID	POZ_SUPPLIER_SITES_ALL_M
ITEM_ID	EGP_SYSTEM_ITEMS_B
CATEGORY_ID	EGP_CATEGORIES_B
REQUESTER_ID	PER_ALL_PEOPLE_F
DESTINATION_ORGANIZATION_ID	HR_ALL_ORGANIZATION_UNITS

Common Tables Used for Joins

Table	Join Condition
POR_REQUISITION_HEADERS_ALL	POR_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID = POR_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
PO_HEADERS_ALL	POR_REQUISITION_LINES_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
PO_LINES_ALL	POR_REQUISITION_LINES_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
PO_LINE_LOCATIONS_ALL	POR_REQUISITION_LINES_ALL.LINE_LOCATION_ID = PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
POZ_SUPPLIERS	POR_REQUISITION_LINES_ALL.VENDOR_ID = POZ_SUPPLIERS.VENDOR_ID
POZ_SUPPLIER_SITES_ALL_M	POR_REQUISITION_LINES_ALL.VENDOR_SITE_ID = POZ_SUPPLIER_SITES_ALL_M.VENDOR_SITE_ID
POR_REQ_DISTRIBUTIONS_ALL	POR_REQUISITION_LINES_ALL.REQUISITION_LINE_ID = POR_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID
RCV_TRANSACTIONS	POR_REQUISITION_LINES_ALL.REQUISITION_LINE_ID = RCV_TRANSACTIONS.REQUISITION_LINE_ID

Important Flow


POR_REQUISITION_HEADERS_ALL
        ↓
POR_REQUISITION_LINES_ALL
        ↓
POR_REQ_DISTRIBUTIONS_ALL
        ↓
PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
RCV_TRANSACTIONS

Example Query

SELECT
    prha.requisition_number,
    prla.line_number,
    prla.item_description,
    prla.quantity,
    prla.unit_price,
    prla.line_status
FROM por_requisition_headers_all prha
JOIN por_requisition_lines_all prla
    ON prha.requisition_header_id = prla.requisition_header_id
WHERE prla.cancel_flag IS NULL;

POR_REQ_DISTRIBUTIONS_ALL (Requisition Distribution Table)

POR_REQ_DISTRIBUTIONS_ALL stores accounting distribution information for requisition lines in Oracle Fusion Procurement. It contains charge accounts, project costing details, budgetary control information, tax amounts, and allocation splits.

Core Identifiers

Column	Description
DISTRIBUTION_ID	Primary Key for Requisition Distribution
REQUISITION_LINE_ID	Reference to Requisition Line
DISTRIBUTION_NUMBER	Distribution Number
REQ_BU_ID	Business Unit Identifier
PRIMARY_LEDGER_ID	Primary Ledger Identifier

Accounting Information

Column	Description
CODE_COMBINATION_ID	Charge Account Combination
ACCRUAL_ACCOUNT_ID	Accrual Account Identifier
VARIANCE_ACCOUNT_ID	Variance Account Identifier
ACCOUNT_USER_OVERRIDE_FLAG	Account Overridden by User
FUNDS_STATUS	Budgetary Control Status
BUDGET_DATE	Budget Reservation Date

Distribution Quantity & Amount

Column	Description
DISTRIBUTION_QUANTITY	Distribution Quantity
PERCENT	Allocation Split Percentage
DISTRIBUTION_AMOUNT	Distribution Amount
DISTRIBUTION_CURRENCY_AMOUNT	Foreign Currency Distribution Amount
RECOVERABLE_TAX	Recoverable Tax Amount
NONRECOVERABLE_TAX	Nonrecoverable Tax Amount
RECOVERABLE_CURRENCY_TAX	Foreign Currency Recoverable Tax
NONRECOVERABLE_CURRENCY_TAX	Foreign Currency Nonrecoverable Tax

Project Costing Information

Column	Description
PJC_PROJECT_ID	Project Identifier
PJC_TASK_ID	Project Task Identifier
PJC_EXPENDITURE_TYPE_ID	Expenditure Type
PJC_EXPENDITURE_ITEM_DATE	Expenditure Item Date
PJC_ORGANIZATION_ID	Project Organization
PJC_WORK_TYPE_ID	Project Work Type
PJC_CONTRACT_ID	Project Contract Identifier
PJC_CONTRACT_LINE_ID	Project Contract Line Identifier
PJC_FUNDING_ALLOCATION_ID	Funding Allocation Identifier
PJC_BILLABLE_FLAG	Billable Indicator
PJC_CAPITALIZABLE_FLAG	Capitalizable Indicator

Status & Control Information

Column	Description
OBJECT_VERSION_NUMBER	Optimistic Lock Version
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Stores accounting distribution level data for requisition lines
One Requisition Line can have multiple distributions
Main table for budgetary control and accounting reporting
Links Procurement with GL, Projects, and Receiving
Used heavily in Procurement accounting analysis
Contains both accounting and project costing information

Primary Foreign Keys

Column	References Table
REQUISITION_LINE_ID	POR_REQUISITION_LINES_ALL
PRIMARY_LEDGER_ID	GL_LEDGERS
CODE_COMBINATION_ID	GL_CODE_COMBINATIONS
ACCRUAL_ACCOUNT_ID	GL_CODE_COMBINATIONS
VARIANCE_ACCOUNT_ID	GL_CODE_COMBINATIONS
PJC_PROJECT_ID	PJF_PROJECTS_ALL_B
PJC_TASK_ID	PJF_TASKS
PJC_CONTRACT_ID	OKC_K_HEADERS_ALL_B

Common Tables Used for Joins

Table	Join Condition
POR_REQUISITION_LINES_ALL	POR_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID = POR_REQUISITION_LINES_ALL.REQUISITION_LINE_ID
POR_REQUISITION_HEADERS_ALL	POR_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID = POR_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
GL_CODE_COMBINATIONS	POR_REQ_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID = GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
RCV_TRANSACTIONS	POR_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID = RCV_TRANSACTIONS.REQ_DISTRIBUTION_ID
RCV_SHIPMENT_LINES	POR_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID = RCV_SHIPMENT_LINES.REQ_DISTRIBUTION_ID
PO_DISTRIBUTIONS_ALL	POR_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID

Important Flow


POR_REQUISITION_HEADERS_ALL
        ↓
POR_REQUISITION_LINES_ALL
        ↓
POR_REQ_DISTRIBUTIONS_ALL
        ↓
PO_DISTRIBUTIONS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
GL_CODE_COMBINATIONS

Example Query

SELECT
    prha.requisition_number,
    prla.line_number,
    prda.distribution_number,
    gcc.concatenated_segments charge_account,
    prda.distribution_amount,
    prda.funds_status
FROM por_requisition_headers_all prha
JOIN por_requisition_lines_all prla
    ON prha.requisition_header_id = prla.requisition_header_id
JOIN por_req_distributions_all prda
    ON prla.requisition_line_id = prda.requisition_line_id
LEFT JOIN gl_code_combinations gcc
    ON prda.code_combination_id = gcc.code_combination_id;

EGP_CATEGORIES_B (Item Categories Base Table)

EGP_CATEGORIES_B stores item category master information in Oracle Fusion SCM. It contains category hierarchy segments, category codes, descriptions, category control flags, and category setup information used across Procurement, Inventory, Product Management, and Costing.

Core Identifiers

Column	Description
CATEGORY_ID	Primary Key for Category
CATEGORY_CODE	Unique Category Code
DESCRIPTION	Category Description
STRUCTURE_ID	Category Structure Identifier
STRUCTURE_INSTANCE_NUMBER	Category Structure Instance
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Category Segments

Column	Description
SEGMENT1	Category Segment 1
SEGMENT2	Category Segment 2
SEGMENT3	Category Segment 3
SEGMENT4	Category Segment 4
SEGMENT5	Category Segment 5
SEGMENT6	Category Segment 6
SEGMENT7	Category Segment 7
SEGMENT8	Category Segment 8
SEGMENT9	Category Segment 9
SEGMENT10	Category Segment 10

Category Status & Control

Column	Description
START_DATE_ACTIVE	Category Active Start Date
END_DATE_ACTIVE	Category Active End Date
WEB_STATUS	Web Publication Status
SUPPLIER_ENABLED_FLAG	Visible in Supplier Profile
CATEGORY_CONTENT_CODE	Allowed Category Content Type
SUMMARY_FLAG	Summary Category Indicator
ENABLED_FLAG	Category Enabled Indicator

Program & ESS Information

Column	Description
REQUEST_ID	ESS Request Identifier
PROGRAM_NAME	Concurrent Program Name
PROGRAM_APP_NAME	Application Name
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Item Category master table in Oracle Fusion SCM
Used across Procurement, Inventory, Product Management, and Costing
One Category can be assigned to multiple items
Category hierarchy is maintained using Segment columns
Frequently used in Spend Analysis and Procurement reporting
Translation values are stored in EGP_CATEGORIES_TL

Primary Foreign Keys

Column	References Table
CATEGORY_ID	EGP_CATEGORIES_TL
STRUCTURE_ID	EGP_CATEGORY_SETS_B

Common Tables Used for Joins

Table	Join Condition
PO_LINES_ALL	EGP_CATEGORIES_B.CATEGORY_ID = PO_LINES_ALL.CATEGORY_ID
POR_REQUISITION_LINES_ALL	EGP_CATEGORIES_B.CATEGORY_ID = POR_REQUISITION_LINES_ALL.CATEGORY_ID
EGP_SYSTEM_ITEMS_B	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID linked through Item Category Assignments
RCV_SHIPMENT_LINES	EGP_CATEGORIES_B.CATEGORY_ID = RCV_SHIPMENT_LINES.CATEGORY_ID
INV_SUPPLY	EGP_CATEGORIES_B.CATEGORY_ID = INV_SUPPLY.CATEGORY_ID
PO_LINE_TYPES_B	EGP_CATEGORIES_B.CATEGORY_ID = PO_LINE_TYPES_B.CATEGORY_ID
EGP_CATEGORIES_TL	EGP_CATEGORIES_B.CATEGORY_ID = EGP_CATEGORIES_TL.CATEGORY_ID
EGP_CATEGORY_SET_VALID_CATS	EGP_CATEGORIES_B.CATEGORY_ID = EGP_CATEGORY_SET_VALID_CATS.CATEGORY_ID

Important Flow


EGP_CATEGORIES_B
        ↓
EGP_SYSTEM_ITEMS_B
        ↓
POR_REQUISITION_LINES_ALL
        ↓
PO_LINES_ALL
        ↓
RCV_SHIPMENT_LINES

Example Query

SELECT
    category_id,
    category_code,
    description,
    segment1,
    segment2,
    web_status
FROM egp_categories_b
WHERE end_date_active IS NULL;

Inventory

EGP_SYSTEM_ITEMS_B (Item Master Table)

EGP_SYSTEM_ITEMS_B stores item master information in Oracle Fusion SCM. It contains item definitions, purchasing controls, inventory attributes, planning controls, receiving settings, costing attributes, manufacturing settings, and item lifecycle information.

Core Identifiers

Column	Description
INVENTORY_ITEM_ID	Primary Key for Item
ITEM_NUMBER	Item Number
ORGANIZATION_ID	Inventory Organization
MASTER_ORG_ID	Master Organization
ITEM_TYPE	Item Type Classification
DESCRIPTION	Item Description
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Item Status & Lifecycle

Column	Description
ENABLED_FLAG	Item Enabled Indicator
START_DATE_ACTIVE	Item Active Start Date
END_DATE_ACTIVE	Item Active End Date
INVENTORY_ITEM_STATUS_CODE	Inventory Item Status
APPROVAL_STATUS	Item Approval Status
CURRENT_PHASE_CODE	Lifecycle Phase
PRELIMINARY_ITEM_FLAG	Preliminary Item Indicator
TEMPLATE_ITEM_FLAG	Template Item Indicator
ENGINEERED_ITEM_FLAG	Engineered Item Indicator

Purchasing & Procurement Attributes

Column	Description
PURCHASING_ITEM_FLAG	Purchasing Item Indicator
PURCHASING_ENABLED_FLAG	Can be added to Purchase Orders
BUYER_ID	Default Buyer
LIST_PRICE_PER_UNIT	Default Purchase Price
MARKET_PRICE	Market Price
NEGOTIATION_REQUIRED_FLAG	Negotiation Required
MUST_USE_APPROVED_VENDOR_FLAG	Approved Supplier Required
MATCH_APPROVAL_LEVEL	Invoice Match Approval Level
INVOICE_MATCH_OPTION	Invoice Matching Option
PURCHASING_TAX_CODE	Purchasing Tax Classification

Inventory Control Information

Column	Description
INVENTORY_ITEM_FLAG	Inventory Item Indicator
STOCK_ENABLED_FLAG	Stock Enabled
MTL_TRANSACTIONS_ENABLED_FLAG	Material Transactions Enabled
INVENTORY_ASSET_FLAG	Inventory Asset Indicator
LOT_CONTROL_CODE	Lot Control
SERIAL_NUMBER_CONTROL_CODE	Serial Number Control
LOT_STATUS_ENABLED	Lot Status Control Enabled
SERIAL_STATUS_ENABLED	Serial Status Control Enabled
PRIMARY_UOM_CODE	Primary Unit of Measure
UNIT_WEIGHT	Unit Weight
UNIT_VOLUME	Unit Volume

Receiving Controls

Column	Description
QTY_RCV_TOLERANCE	Receiving Quantity Tolerance
DAYS_EARLY_RECEIPT_ALLOWED	Allowed Early Receipt Days
DAYS_LATE_RECEIPT_ALLOWED	Allowed Late Receipt Days
RECEIPT_DAYS_EXCEPTION_CODE	Receipt Exception Handling
ALLOW_SUBSTITUTE_RECEIPTS_FLAG	Allow Substitute Receipts
ALLOW_UNORDERED_RECEIPTS_FLAG	Allow Unordered Receipts
ALLOW_EXPRESS_DELIVERY_FLAG	Allow Express Delivery
RECEIVING_ROUTING_ID	Receiving Routing
RECEIVE_CLOSE_TOLERANCE	Receipt Close Tolerance
INVOICE_CLOSE_TOLERANCE	Invoice Close Tolerance

Planning & Replenishment Information

Column	Description
PLANNER_CODE	Planner Code
PLANNING_MAKE_BUY_CODE	Make or Buy Indicator
INVENTORY_PLANNING_CODE	Inventory Planning Method
MRP_PLANNING_CODE	MRP Planning Method
MINIMUM_ORDER_QUANTITY	Minimum Order Quantity
MAXIMUM_ORDER_QUANTITY	Maximum Order Quantity
FIXED_ORDER_QUANTITY	Fixed Order Quantity
FIXED_LOT_MULTIPLIER	Fixed Lot Multiplier
FIXED_LEAD_TIME	Fixed Lead Time
FULL_LEAD_TIME	Total Lead Time

Shipping & Order Management

Column	Description
SHIPPABLE_ITEM_FLAG	Shippable Item
CUSTOMER_ORDER_FLAG	Customer Orderable
CUSTOMER_ORDER_ENABLED_FLAG	Customer Ordering Enabled
INTERNAL_ORDER_FLAG	Internal Order Enabled
DEFAULT_SHIPPING_ORG	Default Shipping Organization
ORDERABLE_ON_WEB_FLAG	Web Orderable Item
BACK_ORDERABLE_FLAG	Backorder Allowed
OVER_SHIPMENT_TOLERANCE	Over Shipment Tolerance
UNDER_SHIPMENT_TOLERANCE	Under Shipment Tolerance

Costing & Financial Information

Column	Description
COSTING_ENABLED_FLAG	Costing Enabled
EXPENSE_ACCOUNT	Expense Account
SALES_ACCOUNT	Sales Account
ASSET_CATEGORY_ID	Asset Category
TAXABLE_FLAG	Taxable Item
TAX_CODE	Tax Code
CREATE_FIXED_ASSET	Fixed Asset Creation Method

Manufacturing Attributes

Column	Description
BUILD_IN_WIP_FLAG	Build in WIP
BOM_ITEM_TYPE	BOM Item Type
REPLENISH_TO_ORDER_FLAG	Replenish to Order
ATP_FLAG	Available to Promise Enabled
ATP_RULE_ID	ATP Rule
WIP_SUPPLY_TYPE	WIP Supply Type
WIP_SUPPLY_SUBINVENTORY	WIP Supply Subinventory

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Request Identifier
PROGRAM_NAME	Concurrent Program Name
JOB_DEFINITION_NAME	ESS Job Definition Name

Important Notes

Main Item Master table in Oracle Fusion SCM
Stores one record per Item and Organization combination
Used across Procurement, Inventory, Manufacturing, Order Management, and Costing
Controls purchasing, receiving, inventory, planning, and manufacturing behavior
Item descriptions/translations are stored in EGP_SYSTEM_ITEMS_TL
Category assignments are maintained in EGP_ITEM_CAT_ASSIGNMENTS

Primary Foreign Keys

Column	References Table
BUYER_ID	PO_AGENTS
ITEM_CATALOG_GROUP_ID	EGP_ITEM_CATALOG_GROUPS_B
DEFAULT_SHIPPING_ORG	INV_ORGANIZATION_PARAMETERS
ORGANIZATION_ID	INV_ORGANIZATION_PARAMETERS
ASSET_CATEGORY_ID	FA_CATEGORIES_B
ATP_RULE_ID	MRP_ATP_RULES

Common Tables Used for Joins

Table	Join Condition
EGP_ITEM_CAT_ASSIGNMENTS	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = EGP_ITEM_CAT_ASSIGNMENTS.INVENTORY_ITEM_ID
EGP_CATEGORIES_B	EGP_ITEM_CAT_ASSIGNMENTS.CATEGORY_ID = EGP_CATEGORIES_B.CATEGORY_ID
PO_LINES_ALL	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = PO_LINES_ALL.ITEM_ID
POR_REQUISITION_LINES_ALL	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = POR_REQUISITION_LINES_ALL.ITEM_ID
RCV_TRANSACTIONS	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = RCV_TRANSACTIONS.ITEM_ID
INV_MATERIAL_TXNS	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = INV_MATERIAL_TXNS.INVENTORY_ITEM_ID
EGP_SYSTEM_ITEMS_TL	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_TL.INVENTORY_ITEM_ID

Important Flow


EGP_SYSTEM_ITEMS_B
        ↓
EGP_ITEM_CAT_ASSIGNMENTS
        ↓
EGP_CATEGORIES_B
        ↓
POR_REQUISITION_LINES_ALL
        ↓
PO_LINES_ALL
        ↓
RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS

Example Query

SELECT
    inventory_item_id,
    item_number,
    item_type,
    primary_uom_code,
    inventory_item_status_code,
    purchasing_enabled_flag
FROM egp_system_items_b
WHERE enabled_flag = 'Y';

EGP_SYSTEM_ITEMS_TL (Item Translation Table)

EGP_SYSTEM_ITEMS_TL stores translated item descriptions and multilingual item text information in Oracle Fusion SCM. It contains item descriptions, long descriptions, HTML formatted descriptions, and language-specific item data.

Core Identifiers

Column	Description
INVENTORY_ITEM_ID	Reference to Inventory Item
ORGANIZATION_ID	Inventory Organization
LANGUAGE	Translation Language Code
SOURCE_LANG	Source Language Code
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Item Description Information

Column	Description
DESCRIPTION	Translated Item Description
LONG_DESCRIPTION	Detailed Item Description
HTML_LONG_DESCRIPTION	HTML Formatted Item Description
TEMPLATE_NAME	Item Template Name

Language Information

Column	Description
LANGUAGE	Language of Translation
SOURCE_LANG	Original Source Language

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Translation table for EGP_SYSTEM_ITEMS_B
Stores multilingual item descriptions
One item can have multiple language translations
Used in Procurement, Inventory, Product Management, and Order Management
Supports both plain text and HTML formatted descriptions
Frequently used for multilingual reporting and UI display

Primary Foreign Keys

Column	References Table
INVENTORY_ITEM_ID	EGP_SYSTEM_ITEMS_B
ORGANIZATION_ID	EGP_SYSTEM_ITEMS_B

Common Tables Used for Joins

Table	Join Condition
EGP_SYSTEM_ITEMS_B	EGP_SYSTEM_ITEMS_TL.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID AND EGP_SYSTEM_ITEMS_TL.ORGANIZATION_ID = EGP_SYSTEM_ITEMS_B.ORGANIZATION_ID
PO_LINES_ALL	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = PO_LINES_ALL.ITEM_ID
POR_REQUISITION_LINES_ALL	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = POR_REQUISITION_LINES_ALL.ITEM_ID
RCV_TRANSACTIONS	EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = RCV_TRANSACTIONS.ITEM_ID

Important Flow


EGP_SYSTEM_ITEMS_B
        ↓
EGP_SYSTEM_ITEMS_TL
        ↓
POR_REQUISITION_LINES_ALL
        ↓
PO_LINES_ALL
        ↓
RCV_TRANSACTIONS

Example Query

SELECT
    esi.item_number,
    esit.language,
    esit.description,
    esit.long_description
FROM egp_system_items_b esi
JOIN egp_system_items_tl esit
    ON esi.inventory_item_id = esit.inventory_item_id
   AND esi.organization_id = esit.organization_id
WHERE esit.language = 'US';

INV_ORGANIZATION_DEFINITIONS_V (Inventory Organization Definitions View)

INV_ORGANIZATION_DEFINITIONS_V stores Inventory Organization setup and organizational control information in Oracle Fusion SCM. It contains organization details, business unit mappings, inventory controls, legal entity information, manufacturing settings, warehouse management controls, and organizational accounting setup.

Core Identifiers

Column	Description
ORGANIZATION_ID	Primary Key for Inventory Organization
ORGANIZATION_CODE	Inventory Organization Code
ORGANIZATION_NAME	Inventory Organization Name
ORGANIZATION_TYPE	Organization Type
MASTER_ORGANIZATION_ID	Master Organization Identifier
ITEM_DEFINITION_ORG_ID	Item Definition Organization

Business Unit & Legal Entity Information

Column	Description
BUSINESS_UNIT_ID	Business Unit Identifier
BUSINESS_UNIT_NAME	Business Unit Name
BUSINESS_GROUP_ID	Business Group Identifier
LEGAL_ENTITY	Legal Entity Name
PROFIT_CENTER_BU_ID	Profit Center Business Unit
PARTY_ID	Trading Community Party Identifier

Accounting & Financial Information

Column	Description
SET_OF_BOOKS_ID	Ledger / Set of Books
CHART_OF_ACCOUNTS_ID	Chart of Accounts Identifier
CURRENCY_CODE	Functional Currency
PERIOD_SET_NAME	Accounting Calendar
FA_BOOK_TYPE_CODE	Fixed Assets Book Type

Inventory & Warehouse Controls

Column	Description
INVENTORY_ENABLED_FLAG	Inventory Enabled Indicator
INVENTORY_FLAG	Inventory Organization Indicator
DISTRIBUTED_ORGANIZATION_FLAG	Distributed Organization Indicator
ALLOW_ITEM_SUBSTITUTIONS	Allow Item Substitutions
TRACK_COUNTRY_OF_ORIGIN_FLAG	Track Country of Origin
PROJECT_REFERENCE_ENABLED	Project Reference Tracking Enabled
FILL_KILL_TRANSFER_ORDERS_FLAG	Fill/Kill Transfer Orders Enabled
FILL_KILL_SALES_ORDER_FLAG	Fill/Kill Sales Orders Enabled
EXPIRED_LOT_SUBINVENTORY	Expired Lot Subinventory
EXPIRED_LOT_LOCATOR	Expired Lot Locator

Manufacturing & Maintenance Controls

Column	Description
MFG_PLANT_FLAG	Manufacturing Plant Indicator
CONTRACT_MFG_FLAG	Contract Manufacturing Enabled
WMS_WITH_MFG_ALM	Warehouse Management with Manufacturing
EAM_ENABLED_FLAG	Enterprise Asset Management Enabled
MAINT_TECH_WB_ENABLED_FLAG	Maintenance Technician Workbench Enabled
MANUAL_RECEIPT_EXP_AT_DEST	Manual Receipt Expense at Destination

Supplier & Customer Information

Column	Description
SUPPLIER_ID	Supplier Identifier
SUPPLIER_SITE_ID	Supplier Site Identifier
INTERNAL_CUSTOMER_FLAG	Internal Customer Indicator
CUSTOMER_ACCOUNT_NUMBER	Customer Account Number

Location & Timezone Information

Column	Description
LOCATION_ID	Location Identifier
TIMEZONE_CODE	Organization Timezone

Kanban & Grouping Information

Column	Description
KANBAN_CARD_PREFIX	Kanban Card Prefix
KANBAN_CARD_START_NUMBER	Kanban Card Starting Number
KANBAN_DOC_SEQ_ID	Kanban Document Sequence
KANBAN_DOC_SEQ_CAT_CODE	Kanban Document Sequence Category
ITEM_GROUPING_CODE	Item Grouping Code
GROUPING_JOB_STATUS	Grouping Job Status
GROUPING_JOB_ID	Grouping Job Identifier

Status & Dates

Column	Description
USER_DEFINITION_ENABLE_DATE	Organization Enable Date
DISABLE_DATE	Organization Disable Date
LAST_UPDATE_DATE	Last Updated Date
INV_LAST_UPDATE_DATE	Inventory Last Update Date

Audit Columns

Column	Description
CREATED_BY	Created By User
CREATION_DATE	Record Creation Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Inventory Organization setup view in Oracle Fusion SCM
Used across Inventory, Procurement, Manufacturing, Costing, and Order Management
Defines organization-level inventory and operational controls
Stores warehouse, manufacturing, and legal entity mappings
Frequently used in Inventory and Procurement reporting
Acts as central organization reference table in SCM

Primary Foreign Keys

Column	References Table
BUSINESS_UNIT_ID	FUN_ALL_BUSINESS_UNITS_V
SET_OF_BOOKS_ID	GL_LEDGERS
CHART_OF_ACCOUNTS_ID	GL_CHART_OF_ACCOUNTS
MASTER_ORGANIZATION_ID	INV_ORGANIZATION_DEFINITIONS_V
SUPPLIER_ID	POZ_SUPPLIERS
SUPPLIER_SITE_ID	POZ_SUPPLIER_SITES_ALL_M
LOCATION_ID	HZ_LOCATIONS
PARTY_ID	HZ_PARTIES

Common Tables Used for Joins

Table	Join Condition
EGP_SYSTEM_ITEMS_B	INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID = EGP_SYSTEM_ITEMS_B.ORGANIZATION_ID
PO_LINE_LOCATIONS_ALL	INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID = PO_LINE_LOCATIONS_ALL.SHIP_TO_ORGANIZATION_ID
RCV_TRANSACTIONS	INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID = RCV_TRANSACTIONS.ORGANIZATION_ID
INV_MATERIAL_TXNS	INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID = INV_MATERIAL_TXNS.ORGANIZATION_ID
POZ_SUPPLIERS	INV_ORGANIZATION_DEFINITIONS_V.SUPPLIER_ID = POZ_SUPPLIERS.VENDOR_ID
POZ_SUPPLIER_SITES_ALL_M	INV_ORGANIZATION_DEFINITIONS_V.SUPPLIER_SITE_ID = POZ_SUPPLIER_SITES_ALL_M.VENDOR_SITE_ID

Important Flow


INV_ORGANIZATION_DEFINITIONS_V
        ↓
EGP_SYSTEM_ITEMS_B
        ↓
PO_LINE_LOCATIONS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS

Example Query

SELECT
    organization_code,
    organization_name,
    business_unit_name,
    legal_entity,
    inventory_enabled_flag,
    mfg_plant_flag
FROM inv_organization_definitions_v
WHERE disable_date IS NULL;

INV_MATERIAL_TXNS (Inventory Material Transactions Table)

INV_MATERIAL_TXNS stores inventory transaction history in Oracle Fusion SCM. It contains inventory movement transactions, receiving transactions, costing details, transfer information, project costing references, warehouse management details, and inventory accounting data.

Core Identifiers

Column	Description
TRANSACTION_ID	Primary Key for Inventory Transaction
INVENTORY_ITEM_ID	Inventory Item Identifier
ORGANIZATION_ID	Inventory Organization Identifier
TRANSACTION_TYPE_ID	Transaction Type Identifier
TRANSACTION_ACTION_ID	Transaction Action Identifier
TRANSACTION_SOURCE_TYPE_ID	Transaction Source Type
TRANSACTION_SOURCE_ID	Transaction Source Identifier
TRANSACTION_SOURCE_NAME	Transaction Source Name

Transaction Quantity Information

Column	Description
TRANSACTION_QUANTITY	Transaction Quantity
PRIMARY_QUANTITY	Primary UOM Quantity
TRANSACTION_UOM	Transaction Unit of Measure
SECONDARY_TRANSACTION_QUANTITY	Secondary Transaction Quantity
SECONDARY_UOM_CODE	Secondary Unit of Measure
ORIG_TRANSACTION_QUANTITY	Original Transaction Quantity
TRANSACTION_DATE	Transaction Processing Date

Inventory Location Information

Column	Description
SUBINVENTORY_CODE	Subinventory Code
LOCATOR_ID	Inventory Locator Identifier
TRANSFER_SUBINVENTORY	Transfer Subinventory
TRANSFER_LOCATOR_ID	Transfer Locator
SHIP_TO_LOCATION_ID	Ship To Location
SHIP_FROM_LOCATION_ID	Ship From Location
LOCATION_TYPE	Location Type

Transfer & Shipment Information

Column	Description
TRANSFER_TRANSACTION_ID	Related Transfer Transaction
TRANSFER_ORGANIZATION_ID	Transfer Organization
SHIPMENT_NUMBER	Shipment Number
SHIPMENT_LINE_NUMBER	Shipment Line Number
WAYBILL_AIRBILL	Waybill / Airbill Number
FREIGHT_CODE	Freight Code
NUMBER_OF_CONTAINERS	Container Count
TRANSFER_PRICE	Transfer Price
TRANSFER_COST	Transfer Cost
TRANSPORTATION_COST	Transportation Cost

Costing & Accounting Information

Column	Description
ACTUAL_COST	Actual Item Cost
TRANSACTION_COST	Entered Transaction Cost
PRIOR_COST	Previous Item Cost
NEW_COST	New Item Cost
VARIANCE_AMOUNT	Cost Variance Amount
VALUE_CHANGE	Total Cost Value Change
MATERIAL_ACCOUNT	Material Account
MATERIAL_OVERHEAD_ACCOUNT	Material Overhead Account
RESOURCE_ACCOUNT	Resource Account
OVERHEAD_ACCOUNT	Overhead Account
DISTRIBUTION_ACCOUNT_ID	Distribution Account
EXPENSE_ACCOUNT_ID	Expense Account
COSTED_FLAG	Costed Indicator
OPM_COSTED_FLAG	Process Manufacturing Costed Flag

Receiving & Procurement Information

Column	Description
RCV_TRANSACTION_ID	Receiving Transaction Reference
RECEIVING_DOCUMENT	Receiving Document
REQUESTER_ID	Requester Identifier
VENDOR_LOT_NUMBER	Supplier Lot Number
MANUAL_RECEIPT_EXPENSE	Manual Receipt Expense Indicator

Project Costing Information

Column	Description
PROJECT_ID	Project Identifier
TASK_ID	Task Identifier
PJC_PROJECT_ID	Project Costing Project
PJC_TASK_ID	Project Costing Task
PJC_EXPENDITURE_TYPE_ID	Expenditure Type
PJC_ORGANIZATION_ID	Project Organization
PJC_WORK_TYPE_ID	Project Work Type
PJC_BILLABLE_FLAG	Billable Indicator
PJC_CAPITALIZABLE_FLAG	Capitalizable Indicator

Manufacturing & Work Order Information

Column	Description
ASSEMBLY_ASSET_ID	Assembly Asset Identifier
ASSEMBLY_SERIAL_NUMBER	Assembly Serial Number
MOVE_TRANSACTION_ID	Move Transaction Reference
COMPLETION_TRANSACTION_ID	Completion Transaction Reference
OPERATION_SEQ_NUM	Operation Sequence Number
OPERATION_TRANSACTION_ID	Operation Transaction Identifier
WIP_SUPPLY_TYPE	WIP Supply Type
FINAL_COMPLETION_FLAG	Final Completion Indicator

Warehouse Management Information

Column	Description
LPN_ID	License Plate Number Identifier
TRANSFER_LPN_ID	Transfer License Plate Number
LICENSE_PLATE_NUMBER	Packing Unit Number
PICK_SLIP_NUMBER	Pick Slip Number
PICK_SLIP_DATE	Pick Slip Date
PICK_RULE_ID	Picking Rule Identifier
PUT_AWAY_RULE_ID	Put Away Rule Identifier
DELIVERY_CART_ID	Delivery Cart Identifier
ZONE_ID	Warehouse Zone Identifier

Tax Information

Column	Description
DEFAULT_TAXATION_COUNTRY	Default Taxation Country
TAX_CLASSIFICATION_CODE	Tax Classification Code
PRODUCT_FISC_CLASSIFICATION	Product Fiscal Classification
PRODUCT_CATEGORY	Product Category
PRODUCT_TYPE	Product Type
TAX_INVOICE_NUMBER	Tax Invoice Number
TAX_INVOICE_DATE	Tax Invoice Date
ASSESSABLE_VALUE	Assessable Value

Status & Processing Information

Column	Description
TRANSACTION_MODE	Transaction Processing Mode
TRANSACTION_BATCH_ID	Transaction Batch Identifier
TRANSACTION_GROUP_ID	Transaction Group Identifier
ERROR_CODE	Error Code
ERROR_EXPLANATION	Error Explanation
IS_EVENT_PUBLISHED	Event Published Indicator
ALM_INTERFACE_STATUS	ALM Interface Status
ALM_INTERFACE_ERROR_CODE	ALM Interface Error Code
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Important Notes

Main Inventory Transaction History table in Oracle Fusion SCM
Stores every inventory movement transaction
Used across Inventory, Procurement, Costing, Manufacturing, and Warehouse Management
Tracks inventory receipts, issues, transfers, adjustments, and cost updates
One of the most important transaction tables in SCM reporting
Contains both operational and costing transaction data

Primary Foreign Keys

Column	References Table
INVENTORY_ITEM_ID	EGP_SYSTEM_ITEMS_B
ORGANIZATION_ID	INV_ORGANIZATION_DEFINITIONS_V
RCV_TRANSACTION_ID	RCV_TRANSACTIONS
DISTRIBUTION_ACCOUNT_ID	GL_CODE_COMBINATIONS
PROJECT_ID	PJF_PROJECTS_ALL_B
TASK_ID	PJF_TASKS
CATEGORY_ID	EGP_CATEGORIES_B
LOCATOR_ID	INV_ITEM_LOCATIONS

Common Tables Used for Joins

Table	Join Condition
EGP_SYSTEM_ITEMS_B	INV_MATERIAL_TXNS.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
INV_ORGANIZATION_DEFINITIONS_V	INV_MATERIAL_TXNS.ORGANIZATION_ID = INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID
RCV_TRANSACTIONS	INV_MATERIAL_TXNS.RCV_TRANSACTION_ID = RCV_TRANSACTIONS.TRANSACTION_ID
PO_HEADERS_ALL	RCV_TRANSACTIONS.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
PO_LINES_ALL	RCV_TRANSACTIONS.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
INV_TRANSACTION_LOT_NUMBERS	INV_MATERIAL_TXNS.TRANSACTION_ID = INV_TRANSACTION_LOT_NUMBERS.TRANSACTION_ID
INV_SERIAL_NUMBERS	INV_MATERIAL_TXNS.TRANSACTION_ID = INV_SERIAL_NUMBERS.LAST_TRANSACTION_ID

Important Flow


PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS
        ↓
INV_TRANSACTION_LOT_NUMBERS
        ↓
INV_SERIAL_NUMBERS

Example Query

SELECT
    imt.transaction_id,
    imt.transaction_date,
    esi.item_number,
    imt.transaction_quantity,
    imt.subinventory_code,
    imt.transaction_cost
FROM inv_material_txns imt
JOIN egp_system_items_b esi
    ON imt.inventory_item_id = esi.inventory_item_id
WHERE imt.transaction_date >= SYSDATE - 30;

INV_ONHAND_QUANTITIES_DETAIL (Inventory Onhand Quantities Table)

INV_ONHAND_QUANTITIES_DETAIL stores current onhand inventory balances in Oracle Fusion SCM. It contains item quantity balances by organization, subinventory, locator, lot, revision, project, and ownership details.

Core Identifiers

Column	Description
ONHAND_QUANTITIES_ID	Primary Key for Onhand Record
INVENTORY_ITEM_ID	Inventory Item Identifier
ORGANIZATION_ID	Inventory Organization Identifier
SUBINVENTORY_CODE	Subinventory Code
LOCATOR_ID	Inventory Locator Identifier
LOT_NUMBER	Inventory Lot Number
REVISION	Item Revision

Quantity Information

Column	Description
PRIMARY_TRANSACTION_QUANTITY	Primary Onhand Quantity
TRANSACTION_QUANTITY	Transaction Quantity
TRANSACTION_UOM_CODE	Transaction Unit of Measure
SECONDARY_TRANSACTION_QUANTITY	Secondary Quantity
SECONDARY_UOM_CODE	Secondary Unit of Measure
STANDARD_PACK_QUANTITY	Standard Pack Quantity
STANDARD_PACK_UOM	Standard Pack Unit of Measure

Location & Inventory Tracking

Column	Description
SUBINVENTORY_CODE	Storage Subinventory
LOCATOR_ID	Physical Inventory Locator
LPN_ID	License Plate Number Identifier
COUNTRY_OF_ORIGIN_CODE	Country of Origin

Transaction Reference Information

Column	Description
CREATE_TRANSACTION_ID	Transaction that Created Record
UPDATE_TRANSACTION_ID	Last Transaction that Updated Record
ORIG_SOURCE_TXN_ID	Original Source Transaction

Receiving & Aging Information

Column	Description
DATE_RECEIVED	Date Inventory was Received
ORIG_DATE_RECEIVED	Original Receipt Date
AGING_ONSET_DATE	Consignment Aging Start Date
AGING_EXPIRATION_DATE	Consignment Aging Expiry Date

Consignment & Ownership Information

Column	Description
OWNING_TYPE	Ownership Type
OWNING_ENTITY_ID	Owning Entity Identifier

Project Costing Information

Column	Description
PROJECT_ID	Project Identifier
TASK_ID	Task Identifier
INV_STRIPING_CATEGORY	Inventory Striping Category

Status & Control Information

Column	Description
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Onhand Inventory balance table in Oracle Fusion SCM
Stores current inventory quantity balances
Maintains balances by item, organization, subinventory, locator, and lot
Used heavily in Inventory Availability and Stock reports
Supports consigned inventory and project-driven inventory tracking
Updated automatically through INV_MATERIAL_TXNS

Primary Foreign Keys

Column	References Table
ORGANIZATION_ID	INV_ORG_PARAMETERS
LOCATOR_ID	INV_ITEM_LOCATIONS
SUBINVENTORY_CODE, ORGANIZATION_ID	INV_SECONDARY_INVENTORIES
INVENTORY_ITEM_ID	EGP_SYSTEM_ITEMS_B
CREATE_TRANSACTION_ID	INV_MATERIAL_TXNS
UPDATE_TRANSACTION_ID	INV_MATERIAL_TXNS
PROJECT_ID	PJF_PROJECTS_ALL_B
TASK_ID	PJF_TASKS

Common Tables Used for Joins

Table	Join Condition
EGP_SYSTEM_ITEMS_B	INV_ONHAND_QUANTITIES_DETAIL.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
INV_ORGANIZATION_DEFINITIONS_V	INV_ONHAND_QUANTITIES_DETAIL.ORGANIZATION_ID = INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID
INV_MATERIAL_TXNS	INV_ONHAND_QUANTITIES_DETAIL.CREATE_TRANSACTION_ID = INV_MATERIAL_TXNS.TRANSACTION_ID
INV_ITEM_LOCATIONS	INV_ONHAND_QUANTITIES_DETAIL.LOCATOR_ID = INV_ITEM_LOCATIONS.INVENTORY_LOCATION_ID
INV_SECONDARY_INVENTORIES	INV_ONHAND_QUANTITIES_DETAIL.SUBINVENTORY_CODE = INV_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME
INV_TRANSACTION_LOT_NUMBERS	INV_ONHAND_QUANTITIES_DETAIL.LOT_NUMBER = INV_TRANSACTION_LOT_NUMBERS.LOT_NUMBER

Important Flow


INV_MATERIAL_TXNS
        ↓
INV_ONHAND_QUANTITIES_DETAIL
        ↓
INV_SECONDARY_INVENTORIES
        ↓
INV_ITEM_LOCATIONS

Example Query

SELECT
    iohqd.organization_id,
    iohqd.subinventory_code,
    esi.item_number,
    iohqd.primary_transaction_quantity,
    iohqd.lot_number
FROM inv_onhand_quantities_detail iohqd
JOIN egp_system_items_b esi
    ON iohqd.inventory_item_id = esi.inventory_item_id
WHERE iohqd.primary_transaction_quantity > 0;

INV_TRANSACTION_TYPES_TL (Inventory Transaction Types Translation Table)

INV_TRANSACTION_TYPES_TL stores multilingual transaction type names and descriptions in Oracle Fusion SCM. It provides translated transaction type information used in Inventory, Receiving, Costing, Warehouse Management, and Material Transaction reporting.

Core Identifiers

Column	Description
TRANSACTION_TYPE_ID	Inventory Transaction Type Identifier
LANGUAGE	Translation Language Code
SOURCE_LANG	Original Source Language
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Transaction Type Information

Column	Description
TRANSACTION_TYPE_NAME	Transaction Type Name
DESCRIPTION	Transaction Type Description

Language Information

Column	Description
LANGUAGE	Language of Translation
SOURCE_LANG	Original Translation Source Language

Seed Data Information

Column	Description
SEED_DATA_SOURCE	Seed Data Source
ORA_SEED_SET1	Oracle Internal Seed Context 1
ORA_SEED_SET2	Oracle Internal Seed Context 2

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Translation table for INV_TRANSACTION_TYPES_B
Stores multilingual transaction type names and descriptions
Used heavily in Inventory Transaction reporting
Provides user-friendly transaction names for Inventory movements
Supports multilingual Oracle Fusion SCM environments
Commonly joined with INV_MATERIAL_TXNS for reporting

Primary Foreign Keys

Column	References Table
TRANSACTION_TYPE_ID	INV_TRANSACTION_TYPES_B

Common Tables Used for Joins

Table	Join Condition
INV_TRANSACTION_TYPES_B	INV_TRANSACTION_TYPES_TL.TRANSACTION_TYPE_ID = INV_TRANSACTION_TYPES_B.TRANSACTION_TYPE_ID
INV_MATERIAL_TXNS	INV_TRANSACTION_TYPES_TL.TRANSACTION_TYPE_ID = INV_MATERIAL_TXNS.TRANSACTION_TYPE_ID
RCV_TRANSACTIONS	INV_TRANSACTION_TYPES_TL.TRANSACTION_TYPE_ID = RCV_TRANSACTIONS.TRANSACTION_TYPE_ID
INV_TXN_REQUEST_LINES	INV_TRANSACTION_TYPES_TL.TRANSACTION_TYPE_ID = INV_TXN_REQUEST_LINES.TRANSACTION_TYPE_ID

Important Flow


INV_TRANSACTION_TYPES_B
        ↓
INV_TRANSACTION_TYPES_TL
        ↓
INV_MATERIAL_TXNS
        ↓
RCV_TRANSACTIONS

Example Query

SELECT
    ittl.transaction_type_name,
    ittl.description,
    ittl.language
FROM inv_transaction_types_tl ittl
WHERE ittl.language = 'US';

EGP_ITEM_CATEGORIES (Item Category Assignments View)

EGP_ITEM_CATEGORIES stores item-to-category assignment information in Oracle Fusion SCM. It maps inventory items to category sets and categories, allowing items to be classified for Procurement, Inventory, Costing, Planning, and Reporting purposes.

Core Identifiers

Column	Description
ITEM_CATEGORY_ASSIGNMENT_ID	Primary Key for Item Category Assignment
INVENTORY_ITEM_ID	Inventory Item Identifier
ORGANIZATION_ID	Inventory Organization Identifier
CATEGORY_SET_ID	Category Set Identifier
CATEGORY_ID	Category Identifier
ALT_ITEM_CAT_CODE	Alternate Item Category Code

Category Assignment Information

Column	Description
SEQUENCE_NUMBER	Category Assignment Sequence
START_DATE	Assignment Start Date
END_DATE	Assignment End Date
OBJECT_VERSION_NUMBER	Optimistic Lock Version

ESS & Program Information

Column	Description
REQUEST_ID	ESS Request Identifier
PROGRAM_NAME	Concurrent Program Name
PROGRAM_APP_NAME	Application Name
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Stores Item to Category assignments
One Item can belong to multiple Category Sets
Used heavily in Procurement, Inventory, and Spend Analysis reporting
Links Item Master with Category hierarchy
Category control depends on Category Set control level
Built from EGP_ITEM_CAT_ASSIGNMENTS and related setup tables

Primary Foreign Keys

Column	References Table
INVENTORY_ITEM_ID	EGP_SYSTEM_ITEMS_B
ORGANIZATION_ID	INV_ORGANIZATION_PARAMETERS
CATEGORY_SET_ID	EGP_CATEGORY_SETS_B
CATEGORY_ID	EGP_CATEGORIES_B

Common Tables Used for Joins

Table	Join Condition
EGP_SYSTEM_ITEMS_B	EGP_ITEM_CATEGORIES.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
EGP_CATEGORIES_B	EGP_ITEM_CATEGORIES.CATEGORY_ID = EGP_CATEGORIES_B.CATEGORY_ID
EGP_CATEGORY_SETS_B	EGP_ITEM_CATEGORIES.CATEGORY_SET_ID = EGP_CATEGORY_SETS_B.CATEGORY_SET_ID
PO_LINES_ALL	EGP_ITEM_CATEGORIES.CATEGORY_ID = PO_LINES_ALL.CATEGORY_ID
POR_REQUISITION_LINES_ALL	EGP_ITEM_CATEGORIES.CATEGORY_ID = POR_REQUISITION_LINES_ALL.CATEGORY_ID
INV_MATERIAL_TXNS	EGP_ITEM_CATEGORIES.INVENTORY_ITEM_ID = INV_MATERIAL_TXNS.INVENTORY_ITEM_ID

Important Flow


EGP_SYSTEM_ITEMS_B
        ↓
EGP_ITEM_CATEGORIES
        ↓
EGP_CATEGORIES_B
        ↓
POR_REQUISITION_LINES_ALL
        ↓
PO_LINES_ALL
        ↓
INV_MATERIAL_TXNS

Underlying Query Logic

EGP_ITEM_CAT_ASSIGNMENTS
        +
EGP_CATEGORY_SETS_B
        +
EGP_ITEM_ORG_ASSOCIATIONS
        +
INV_ORG_PARAMETERS

Control Level Logic:
1 = Master Organization
2 = Item Definition Organization

Example Query

SELECT
    eic.inventory_item_id,
    eic.organization_id,
    eic.category_set_id,
    ecb.category_code,
    ecb.description
FROM egp_item_categories eic
JOIN egp_categories_b ecb
    ON eic.category_id = ecb.category_id
WHERE eic.end_date IS NULL;

INV_UNITS_OF_MEASURE_B (Units of Measure Base Table)

INV_UNITS_OF_MEASURE_B stores Unit of Measure (UOM) definitions used across Oracle Fusion SCM. It contains UOM codes, UOM classes, conversion setup, standard pack controls, and base unit definitions used in Inventory, Procurement, Manufacturing, Shipping, and Costing.

Core Identifiers

Column	Description
UNIT_OF_MEASURE_ID	Primary Key for Unit of Measure
UOM_CODE	Short Unit of Measure Code
UOM_CLASS	Unit of Measure Class
OBJECT_VERSION_NUMBER	Optimistic Lock Version

UOM Definition Information

Column	Description
BASE_UOM_FLAG	Base Unit of Measure Indicator
DERIVED_UNIT_PARENT_CODE	Parent UOM Code for Derived Unit
STANDARD_PACK_FLAG	Standard Pack Indicator
HAS_GENERATED_CODE	System Generated Code Indicator
DISABLE_DATE	UOM Disable Date

UOM Classification Information

Column	Description
UOM_CLASS	UOM Classification Group
BASE_UOM_FLAG	Base UOM within Class
DERIVED_UNIT_PARENT_CODE	Derived UOM Parent

ESS & Program Information

Column	Description
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Unit of Measure master table in Oracle Fusion SCM
Used across Inventory, Procurement, Manufacturing, Shipping, and Costing
Defines base and derived units of measure
Supports standard pack and conversion functionality
Translation values are stored in INV_UNITS_OF_MEASURE_TL
Used heavily in item, inventory, and transaction processing

Primary Foreign Keys

Column	References Table
UOM_CLASS	INV_UOM_CLASSES_B
UNIT_OF_MEASURE_ID	INV_UNITS_OF_MEASURE_TL
UOM_CODE	INV_UOM_CONVERSIONS
DERIVED_UNIT_PARENT_CODE	INV_UNITS_OF_MEASURE_B

Common Tables Used for Joins

Table	Join Condition
INV_UNITS_OF_MEASURE_TL	INV_UNITS_OF_MEASURE_B.UNIT_OF_MEASURE_ID = INV_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE_ID
EGP_SYSTEM_ITEMS_B	INV_UNITS_OF_MEASURE_B.UOM_CODE = EGP_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE
INV_MATERIAL_TXNS	INV_UNITS_OF_MEASURE_B.UOM_CODE = INV_MATERIAL_TXNS.TRANSACTION_UOM
PO_LINES_ALL	INV_UNITS_OF_MEASURE_B.UOM_CODE = PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE
POR_REQUISITION_LINES_ALL	INV_UNITS_OF_MEASURE_B.UOM_CODE = POR_REQUISITION_LINES_ALL.UOM_CODE
INV_UOM_CONVERSIONS	INV_UNITS_OF_MEASURE_B.UOM_CODE = INV_UOM_CONVERSIONS.UOM_CODE
INV_UOM_CLASS_CONVERSIONS	INV_UNITS_OF_MEASURE_B.UOM_CODE = INV_UOM_CLASS_CONVERSIONS.FROM_UOM_CODE

Important Flow


INV_UNITS_OF_MEASURE_B
        ↓
INV_UOM_CONVERSIONS
        ↓
EGP_SYSTEM_ITEMS_B
        ↓
PO_LINES_ALL
        ↓
INV_MATERIAL_TXNS

Example Query

SELECT
    uom_code,
    uom_class,
    base_uom_flag,
    standard_pack_flag,
    disable_date
FROM inv_units_of_measure_b
WHERE disable_date IS NULL;

Order Management

DOO_HEADERS_ALL (Sales Order Headers Table)

DOO_HEADERS_ALL stores sales order header information in Oracle Fusion Order Management. It contains customer details, order status, pricing information, shipping controls, fulfillment setup, orchestration details, currency information, and order lifecycle tracking data.

Core Identifiers

Column	Description
HEADER_ID	Primary Key for Sales Order Header
ORDER_NUMBER	Sales Order Number
SOURCE_ORDER_NUMBER	Source System Order Number
SOURCE_ORDER_ID	Source System Order Identifier
SOURCE_ORDER_SYSTEM	Source Order Capture System
SOURCE_REVISION_NUMBER	Source Order Revision Number
ORG_ID	Business Unit Identifier
LEGAL_ENTITY_ID	Legal Entity Identifier

Customer Information

Column	Description
SOLD_TO_CUSTOMER_ID	Sold To Customer Identifier
SOLD_TO_CONTACT_ID	Sold To Contact Identifier
SOLD_TO_PARTY_ID	Sold To Party Identifier
SOLD_TO_PARTY_CONTACT_ID	Sold To Party Contact Identifier
SOLD_TO_PARTY_CONTACT_POINT_ID	Sold To Contact Point Identifier
CUSTOMER_PO_NUMBER	Customer Purchase Order Number
SALES_CHANNEL_CODE	Sales Channel
SALESPERSON_ID	Salesperson Identifier

Order Details

Column	Description
ORDER_TYPE_CODE	Order Type
ORDERED_DATE	Order Date
STATUS_CODE	Order Status
OPEN_FLAG	Open Order Indicator
CANCELED_FLAG	Cancelled Order Indicator
ON_HOLD	Order Hold Indicator
SUBMITTED_FLAG	Submitted Indicator
SUBMITTED_DATE	Order Submission Date
SUBMITTED_BY	User who Submitted Order
MODIFIED_FLAG	Order Modified Indicator
CHANGE_VERSION_NUMBER	Order Change Version

Pricing & Currency Information

Column	Description
TRANSACTIONAL_CURRENCY_CODE	Transaction Currency
APPLIED_CURRENCY_CODE	Applied Pricing Currency
CONVERSION_RATE	Currency Conversion Rate
CONVERSION_TYPE_CODE	Currency Conversion Type
CONVERSION_DATE	Currency Conversion Date
PRICED_ON	Pricing Execution Date
PRICING_DATE	Pricing Effective Date
PRICING_SEGMENT_CODE	Pricing Segment
PRICING_STRATEGY_ID	Pricing Strategy Identifier
FREEZE_PRICE_FLAG	Freeze Price Indicator
ALLOW_CURRENCY_OVERRIDE_FLAG	Allow Currency Override

Shipping & Fulfillment Information

Column	Description
FULFILL_ORG_ID	Fulfillment Organization
PARTIAL_SHIP_ALLOWED_FLAG	Partial Shipment Allowed
SHIPSET_FLAG	Ship Set Indicator
SHIPMENT_PRIORITY_CODE	Shipment Priority
REQUEST_SHIP_DATE	Requested Ship Date
LATEST_ACCEPTABLE_SHIP_DATE	Latest Acceptable Ship Date
EARLIEST_ACCEPTABLE_SHIP_DATE	Earliest Acceptable Ship Date
REQUEST_ARRIVAL_DATE	Requested Arrival Date
LATEST_ACCEPT_ARRIVAL_DATE	Latest Acceptable Arrival Date
EARLIEST_ACCEPT_ARRIVAL_DATE	Earliest Acceptable Arrival Date
CARRIER_ID	Carrier Identifier
SHIP_MODE_OF_TRANSPORT	Mode of Transport
SHIP_CLASS_OF_SERVICE	Shipping Service Level
FREIGHT_TERMS_CODE	Freight Terms
FOB_POINT_CODE	FOB Point
PACKING_INSTRUCTIONS	Packing Instructions
SHIPPING_INSTRUCTIONS	Shipping Instructions

Procurement & Supply Information

Column	Description
SUPPLIER_ID	Supplier Identifier
SUPPLIER_SITE_ID	Supplier Site Identifier
SUBINVENTORY	Shipping Subinventory
DEMAND_CLASS_CODE	Demand Class
SUBSTITUTE_ALLOWED_FLAG	Substitution Allowed

Approval & Credit Information

Column	Description
PRE_CREDIT_CHECKED_FLAG	Pre Credit Checked Indicator
APPROVAL_SEQUENCE_NUMBER	Approval Sequence Number
PAYMENT_TERM_ID	Payment Term Identifier
FREEZE_TAX_FLAG	Freeze Tax Indicator
FREEZE_SHIPPING_CHARGE_FLAG	Freeze Shipping Charges

Revision & Cancellation Information

Column	Description
REVISION_REASON_CODE	Order Revision Reason
REVISION_COMMENTS	Revision Comments
CANCEL_REASON_CODE	Cancellation Reason
REQUEST_CANCEL_DATE	Requested Cancellation Date
RETURN_COMMENTS	Return Comments

System & Processing Information

Column	Description
OWNER_ID	Order Owner
CREATION_MODE	Order Creation Mode
BATCH_ID	Import Batch Identifier
MDO_FLAG	Multi-Domain Orchestration Indicator
IS_EDITABLE	Editable Indicator
HEADER_UPDATE_ALLOWED_FLAG	Header Update Allowed
LINE_ADD_ALLOWED_FLAG	Line Add Allowed
AUTO_SAVED_FLAG	Auto Saved Indicator
REDWOOD_ELIGIBLE_FLAG	Redwood Eligible Indicator

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Important Notes

Main Sales Order Header table in Oracle Fusion Order Management
Stores one record per Sales Order
Line information is stored in DOO_LINES_ALL
Used across Order Management, Shipping, Pricing, and Fulfillment
Tracks pricing, shipping, orchestration, and customer information
Central table for Sales Order reporting and integrations

Primary Foreign Keys

Column	References Table
HEADER_ID	DOO_LINES_ALL
ORG_ID	FUN_ALL_BUSINESS_UNITS_V
LEGAL_ENTITY_ID	XLE_ENTITY_PROFILES
SOLD_TO_PARTY_ID	HZ_PARTIES
FULFILL_ORG_ID	INV_ORGANIZATION_DEFINITIONS_V
SUPPLIER_ID	POZ_SUPPLIERS
SUPPLIER_SITE_ID	POZ_SUPPLIER_SITES_ALL_M
PAYMENT_TERM_ID	RA_TERMS_B
CARRIER_ID	WSH_CARRIERS

Common Tables Used for Joins

Table	Join Condition
DOO_LINES_ALL	DOO_HEADERS_ALL.HEADER_ID = DOO_LINES_ALL.HEADER_ID
DOO_ORDER_ADDRESSES	DOO_HEADERS_ALL.HEADER_ID = DOO_ORDER_ADDRESSES.HEADER_ID
DOO_ORDER_TOTALS	DOO_HEADERS_ALL.HEADER_ID = DOO_ORDER_TOTALS.HEADER_ID
HZ_PARTIES	DOO_HEADERS_ALL.SOLD_TO_PARTY_ID = HZ_PARTIES.PARTY_ID
INV_ORGANIZATION_DEFINITIONS_V	DOO_HEADERS_ALL.FULFILL_ORG_ID = INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID
POZ_SUPPLIERS	DOO_HEADERS_ALL.SUPPLIER_ID = POZ_SUPPLIERS.VENDOR_ID
DOO_PAYMENTS	DOO_HEADERS_ALL.HEADER_ID = DOO_PAYMENTS.HEADER_ID

Important Flow


DOO_HEADERS_ALL
        ↓
DOO_LINES_ALL
        ↓
DOO_ORDER_ADDRESSES
        ↓
WSH_DELIVERY_DETAILS
        ↓
INV_MATERIAL_TXNS

Example Query

SELECT
    order_number,
    ordered_date,
    status_code,
    transactional_currency_code,
    customer_po_number
FROM doo_headers_all
WHERE open_flag = 'Y';

DOO_LINES_ALL (Sales Order Lines Table)

DOO_LINES_ALL stores sales order line information in Oracle Fusion Order Management. It contains item details, quantities, pricing information, shipping schedules, fulfillment details, orchestration hierarchy, inventory organization references, and line-level order lifecycle data.

Core Identifiers

Column	Description
LINE_ID	Primary Key for Sales Order Line
HEADER_ID	Reference to Sales Order Header
LINE_NUMBER	Sales Order Line Number
DISPLAY_LINE_NUMBER	Display Line Number
SOURCE_LINE_ID	Source System Line Identifier
SOURCE_LINE_NUMBER	Source System Line Number
SOURCE_ORDER_ID	Source Order Identifier
SOURCE_ORDER_NUMBER	Source Order Number
SOURCE_ORDER_SYSTEM	Source Order System

Item Information

Column	Description
INVENTORY_ITEM_ID	Inventory Item Identifier
ITEM_TYPE_CODE	Item Type
ITEM_SUB_TYPE_CODE	Item Sub Type
LINE_TYPE_CODE	Order Line Type
CATEGORY_CODE	Category Code
ORDERED_UOM	Ordered Unit of Measure
QUANTITY_PER_MODEL	Component Quantity Per Model

Quantity Information

Column	Description
ORDERED_QTY	Ordered Quantity
FULFILLED_QTY	Fulfilled Quantity
SHIPPED_QTY	Shipped Quantity
CANCELED_QTY	Cancelled Quantity
RMA_DELIVERED_QTY	Returned Quantity Delivered

Pricing Information

Column	Description
UNIT_LIST_PRICE	Base List Price
UNIT_SELLING_PRICE	Selling Price Per Unit
EXTENDED_AMOUNT	Total Extended Amount

Shipping & Fulfillment Information

Column	Description
SCHEDULE_SHIP_DATE	Scheduled Ship Date
ACTUAL_SHIP_DATE	Actual Ship Date
FULFILLMENT_DATE	Fulfillment Completion Date
INVENTORY_ORGANIZATION_ID	Inventory Organization
OPEN_FLAG	Open Line Indicator
CANCELED_FLAG	Cancelled Line Indicator
ON_HOLD	Line Hold Indicator
STATUS_CODE	Line Status

Hierarchy & Model Information

Column	Description
PARENT_LINE_ID	Parent Line Identifier
ROOT_PARENT_LINE_ID	Root Parent Line Identifier
COMP_SEQ_PATH	Model Component Hierarchy Path
TRANSFORM_FROM_LINE_ID	Transformed From Line
REFERENCE_LINE_ID	Reference Line Identifier

Change & Version Information

Column	Description
MODIFIED_FLAG	Modified Indicator
DELTA_TYPE	Change Delta Type
SOURCE_REVISION_NUMBER	Source Revision Number
OBJECT_VERSION_NUMBER	Optimistic Lock Version
CREATED_IN_RELEASE	Created Release Version

Organization Information

Column	Description
ORG_ID	Business Unit Identifier
SOURCE_ORG_ID	Source Organization Identifier
INVENTORY_ORGANIZATION_ID	Inventory Organization Identifier

External Reference Information

Column	Description
ORIG_SYS_DOCUMENT_REF	Original External Document Reference
ORIG_SYS_DOCUMENT_LINE_REF	Original External Line Reference
SOURCE_SCHEDULE_ID	Source Schedule Identifier
SOURCE_SCHEDULE_NUMBER	Source Schedule Number

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
OWNER_ID	Order Line Owner

Important Notes

Main Sales Order Line table in Oracle Fusion Order Management
Stores one record per Sales Order Line
Contains item, quantity, pricing, and fulfillment information
Supports model and configured item hierarchies
Frequently used in Shipping, Fulfillment, and Revenue reporting
Linked directly with DOO_HEADERS_ALL and fulfillment tables

Primary Foreign Keys

Column	References Table
HEADER_ID	DOO_HEADERS_ALL
LINE_ID	DOO_FULFILL_LINES_ALL
INVENTORY_ITEM_ID	EGP_SYSTEM_ITEMS_B
INVENTORY_ORGANIZATION_ID	INV_ORGANIZATION_DEFINITIONS_V
PARENT_LINE_ID	DOO_LINES_ALL
ROOT_PARENT_LINE_ID	DOO_LINES_ALL
ORG_ID	FUN_ALL_BUSINESS_UNITS_V

Common Tables Used for Joins

Table	Join Condition
DOO_HEADERS_ALL	DOO_LINES_ALL.HEADER_ID = DOO_HEADERS_ALL.HEADER_ID
DOO_FULFILL_LINES_ALL	DOO_LINES_ALL.LINE_ID = DOO_FULFILL_LINES_ALL.LINE_ID
EGP_SYSTEM_ITEMS_B	DOO_LINES_ALL.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
INV_ORGANIZATION_DEFINITIONS_V	DOO_LINES_ALL.INVENTORY_ORGANIZATION_ID = INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID
WSH_DELIVERY_DETAILS	DOO_LINES_ALL.LINE_ID = WSH_DELIVERY_DETAILS.SOURCE_LINE_ID
INV_MATERIAL_TXNS	DOO_LINES_ALL.INVENTORY_ITEM_ID = INV_MATERIAL_TXNS.INVENTORY_ITEM_ID

Important Flow


DOO_HEADERS_ALL
        ↓
DOO_LINES_ALL
        ↓
DOO_FULFILL_LINES_ALL
        ↓
WSH_DELIVERY_DETAILS
        ↓
INV_MATERIAL_TXNS

Example Query

SELECT
    dhl.order_number,
    dll.line_number,
    dll.ordered_qty,
    dll.unit_selling_price,
    dll.status_code
FROM doo_headers_all dhl
JOIN doo_lines_all dll
    ON dhl.header_id = dll.header_id
WHERE dll.open_flag = 'Y';

DOO_FULFILL_LINES_ALL (Fulfillment Lines Table)

DOO_FULFILL_LINES_ALL stores fulfillment execution details for sales order lines in Oracle Fusion Order Management. It contains shipping, reservation, scheduling, invoicing, procurement, fulfillment orchestration, inventory, pricing, tax, subscription, and supply chain execution data.

Core Identifiers

Column	Description
FULFILL_LINE_ID	Primary Key for Fulfillment Line
HEADER_ID	Sales Order Header Identifier
LINE_ID	Sales Order Line Identifier
FULFILL_LINE_NUMBER	Fulfillment Line Number
SOURCE_ORDER_NUMBER	Source Order Number
SOURCE_LINE_NUMBER	Source Order Line Number
STATUS_CODE	Fulfillment Status
OWNER_ID	Fulfillment Owner

Item & Quantity Information

Column	Description
INVENTORY_ITEM_ID	Inventory Item Identifier
ITEM_TYPE_CODE	Item Type
ITEM_SUB_TYPE_CODE	Item Sub Type
ORDERED_QTY	Ordered Quantity
FULFILLED_QTY	Fulfilled Quantity
SHIPPED_QTY	Shipped Quantity
RESERVED_QTY	Reserved Quantity
CANCELED_QTY	Cancelled Quantity
ORDERED_UOM	Ordered Unit of Measure
SHIPPED_UOM	Shipped Unit of Measure
SECONDARY_UOM	Secondary Unit of Measure
SECONDARY_ORDERED_QTY	Secondary Ordered Quantity
SECONDARY_SHIPPED_QTY	Secondary Shipped Quantity

Pricing & Financial Information

Column	Description
UNIT_LIST_PRICE	List Price Per Unit
UNIT_SELLING_PRICE	Selling Price Per Unit
EXTENDED_AMOUNT	Total Fulfillment Amount
PAYMENT_TERM_ID	Payment Term Identifier
ACCOUNTING_RULE_ID	Accounting Rule Identifier
INVOICING_RULE_ID	Invoicing Rule Identifier
ESTIMATE_MARGIN	Estimated Margin
ESTIMATE_FULFILLMENT_COST	Estimated Fulfillment Cost

Shipping & Scheduling Information

Column	Description
REQUEST_SHIP_DATE	Requested Ship Date
SCHEDULE_SHIP_DATE	Scheduled Ship Date
PROMISE_SHIP_DATE	Promised Ship Date
ACTUAL_SHIP_DATE	Actual Ship Date
REQUEST_ARRIVAL_DATE	Requested Arrival Date
SCHEDULE_ARRIVAL_DATE	Scheduled Arrival Date
PROMISE_ARRIVAL_DATE	Promised Arrival Date
FULFILLMENT_DATE	Fulfillment Completion Date
LATEST_ACCEPTABLE_SHIP_DATE	Latest Acceptable Ship Date
EARLIEST_ACCEPTABLE_SHIP_DATE	Earliest Acceptable Ship Date
SHIP_MODE_OF_TRANSPORT	Mode of Transport
SHIP_CLASS_OF_SERVICE	Shipping Service Level
SHIPMENT_PRIORITY_CODE	Shipment Priority
FREIGHT_TERMS_CODE	Freight Terms
FOB_POINT_CODE	FOB Point
PACKING_INSTRUCTIONS	Packing Instructions
SHIPPING_INSTRUCTIONS	Shipping Instructions

Customer & Address Information

Column	Description
BILL_TO_CUSTOMER_ID	Bill To Customer
BILL_TO_SITE_USE_ID	Bill To Customer Site
SHIP_TO_CUSTOMER_ID	Ship To Customer
SHIP_TO_SITE_USE_ID	Ship To Customer Site
SHIP_TO_PARTY_ID	Ship To Party
SHIP_TO_PARTY_SITE_ID	Ship To Party Site
CUSTOMER_PO_NUMBER	Customer Purchase Order Number
CUSTOMER_PO_LINE_NUMBER	Customer PO Line Number

Inventory & Supply Information

Column	Description
INVENTORY_ORGANIZATION_ID	Inventory Organization
FULFILL_ORG_ID	Fulfillment Organization
SUBINVENTORY	Shipping Subinventory
SUPPLIER_ID	Supplier Identifier
SUPPLIER_SITE_ID	Supplier Site Identifier
BUYER_ID	Buyer Identifier
SUPPLY_STATUS_CODE	Supply Status
PO_STATUS_CODE	Purchase Order Status
FULFILLMENT_MODE	Regular / Dropship / Back-to-Back
RESERVATION_ID	Inventory Reservation Identifier

Tax Information

Column	Description
TAX_CLASSIFICATION_CODE	Tax Classification
TAX_EXEMPT_FLAG	Tax Exempt Indicator
TAX_EXEMPTION_REASON_CODE	Tax Exemption Reason
DEFAULT_TAXATION_COUNTRY	Taxation Country
TAX_INVOICE_NUMBER	Tax Invoice Number
TAX_INVOICE_DATE	Tax Invoice Date
ASSESSABLE_VALUE	Assessable Value
PRODUCT_TYPE	Product Type
PRODUCT_CATEGORY	Product Category

Subscription & Contract Information

Column	Description
SUBSCRIPTION_INTERFACED_FLAG	Subscription Interface Status
INTEGRATE_SUBSCRIPTION_FLAG	Subscription Integration Enabled
SUBSCRIPTION_PROFILE_ID	Subscription Profile Identifier
SERVICE_DURATION	Service Duration
SERVICE_DURATION_PERIOD_CODE	Service Duration UOM
CONTRACT_START_DATE	Contract Start Date
CONTRACT_END_DATE	Contract End Date
TOTAL_CONTRACT_QUANTITY	Total Contract Quantity
TOTAL_CONTRACT_AMOUNT	Total Contract Amount

Hierarchy & Configuration Information

Column	Description
PARENT_FULFILL_LINE_ID	Parent Fulfillment Line
ROOT_PARENT_FULFILL_LINE_ID	Root Parent Fulfillment Line
COMP_SEQ_PATH	Configuration Hierarchy Path
CONFIG_ITEM_REFERENCE	Configuration Item Reference
CONFIG_HEADER_ID	Configuration Header
CONFIG_REVISION_NUMBER	Configuration Revision
VALID_CONFIGURATION_FLAG	Valid Configuration Indicator
ONE_TIME_CONFIG_FLAG	One Time Configuration Indicator

Status & Processing Flags

Column	Description
OPEN_FLAG	Open Fulfillment Line Indicator
CANCELED_FLAG	Cancelled Fulfillment Line Indicator
ON_HOLD	Hold Indicator
MODIFIED_FLAG	Modified Indicator
CHANGE_ELIGIBLE_FLAG	Change Eligible Indicator
INVOICE_INTERFACED_FLAG	Invoice Interfaced
INVENTORY_INTERFACED_FLAG	Inventory Interfaced
TRANSPORTATION_PLANNED_FLAG	Transportation Planned
PROMOTION_ITEM_FLAG	Promotion Item Indicator
DRAFT_SCHEDULED_FLAG	Draft Scheduled Indicator

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Important Notes

Main Fulfillment Execution table in Oracle Fusion Order Management
Stores fulfillment, shipping, invoicing, and orchestration details
One Sales Order Line can create multiple Fulfillment Lines
Supports split shipments, dropship, and back-to-back fulfillment
Used heavily in Shipping, Inventory, Billing, and Supply Chain reporting
Central bridge between Order Management and Supply Execution

Primary Foreign Keys

Column	References Table
LINE_ID	DOO_LINES_ALL
HEADER_ID	DOO_HEADERS_ALL
INVENTORY_ITEM_ID	EGP_SYSTEM_ITEMS_B
INVENTORY_ORGANIZATION_ID	INV_ORGANIZATION_DEFINITIONS_V
FULFILL_ORG_ID	INV_ORGANIZATION_DEFINITIONS_V
SUPPLIER_ID	POZ_SUPPLIERS
SUPPLIER_SITE_ID	POZ_SUPPLIER_SITES_ALL_M
PAYMENT_TERM_ID	RA_TERMS_B
ACCOUNTING_RULE_ID	MSC_INVOICING_ACCT_RULES
INVOICING_RULE_ID	MSC_INVOICING_ACCT_RULES

Common Tables Used for Joins

Table	Join Condition
DOO_LINES_ALL	DOO_FULFILL_LINES_ALL.LINE_ID = DOO_LINES_ALL.LINE_ID
DOO_HEADERS_ALL	DOO_FULFILL_LINES_ALL.HEADER_ID = DOO_HEADERS_ALL.HEADER_ID
EGP_SYSTEM_ITEMS_B	DOO_FULFILL_LINES_ALL.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
INV_ORGANIZATION_DEFINITIONS_V	DOO_FULFILL_LINES_ALL.INVENTORY_ORGANIZATION_ID = INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID
WSH_DELIVERY_DETAILS	DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID = WSH_DELIVERY_DETAILS.SOURCE_LINE_ID
INV_MATERIAL_TXNS	DOO_FULFILL_LINES_ALL.INVENTORY_ITEM_ID = INV_MATERIAL_TXNS.INVENTORY_ITEM_ID
DOO_ORDER_ADDRESSES	DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID = DOO_ORDER_ADDRESSES.FULFILL_LINE_ID

Important Flow


DOO_HEADERS_ALL
        ↓
DOO_LINES_ALL
        ↓
DOO_FULFILL_LINES_ALL
        ↓
WSH_DELIVERY_DETAILS
        ↓
INV_MATERIAL_TXNS
        ↓
AR_INVOICE_LINES_ALL

Example Query

SELECT
    dhl.order_number,
    dfl.fulfill_line_number,
    dfl.status_code,
    dfl.ordered_qty,
    dfl.shipped_qty,
    dfl.fulfillment_mode
FROM doo_headers_all dhl
JOIN doo_fulfill_lines_all dfl
    ON dhl.header_id = dfl.header_id
WHERE dfl.open_flag = 'Y';

DOO_ORDER_CHARGES (Order Charges Table)

DOO_ORDER_CHARGES stores pricing and charge information for sales orders in Oracle Fusion Order Management. It contains product charges, shipping charges, subscription pricing, recurring charges, usage pricing, tier pricing, promotion pricing, and pricing adjustment related data.

Core Identifiers

Column	Description
ORDER_CHARGE_ID	Primary Key for Order Charge
PARENT_ENTITY_CODE	Parent Entity Type
PARENT_ENTITY_ID	Parent Entity Identifier
FULFILL_LINE_ID	Fulfillment Line Identifier (Obsolete)
SOURCE_CHARGE_ID	Source System Charge Identifier
REFERENCE_ORDER_CHARGE_ID	Reference Charge for Change Orders

Charge Definition Information

Column	Description
CHARGE_DEFINITION_CODE	Charge Definition Code
CHARGE_TYPE_CODE	Charge Type
CHARGE_SUBTYPE_CODE	Charge Sub Type
PRICE_TYPE_CODE	Price Type
CHARGE_APPLIES_TO	Charge Applies To
PRIMARY_FLAG	Primary Charge Indicator
ROLLUP_FLAG	Rollup Charge Indicator
SEQUENCE_NUMBER	Charge Sequence Number

Pricing Information

Column	Description
CHARGE_CURRENCY_CODE	Charge Currency
GSA_UNIT_PRICE	GSA Unit Price
AVG_UNIT_SELLING_PRICE	Average Unit Selling Price
PRICED_QUANTITY	Priced Quantity
PRICED_QUANTITY_UOM_CODE	Priced Quantity UOM
PRICE_PERIODICITY_CODE	Price Periodicity
USAGE_UOM_CODE	Usage Unit of Measure
CHARGE_PERIOD_CODE	Charge Period

Tier Pricing Information

Column	Description
TIERED_FLAG	Tier Pricing Indicator
TIER_BASIS_TYPE_CODE	Tier Basis Type
TIER_AGGREGATION_METHOD	Tier Aggregation Method
TIER_APPLIES_TO	Tier Applicability
BLOCK_ALLOWANCE	Block Allowance
BLOCK_SIZE	Tier Block Size
USAGE_PRICE_LOCK_FLAG	Usage Price Lock Indicator

Subscription & Recurring Charge Information

Column	Description
PRICE_TYPE_CODE	One-Time / Recurring / Usage Price Type
PRICE_PERIODICITY_CODE	Recurring Billing Frequency
PERIODIC_BILLING_FLAG	Periodic Billing Enabled
PERIODIC_REV_RECOGNITION_FLAG	Periodic Revenue Recognition Enabled
USAGE_PRICE_LOCK_FLAG	Usage Pricing Lock

Promotion & Adjustment Information

Column	Description
CAN_ADJUST_FLAG	Adjustment Allowed Indicator
PROMOTION_ITEM_TERM_ID	Promotion Term Identifier
MODIFIED_FLAG	Modified Charge Indicator
DELTA_TYPE	Change Delta Type

Status & Control Information

Column	Description
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main pricing charge table in Oracle Fusion Order Management
Stores charges for products, shipping, services, and subscriptions
Supports one-time, recurring, and usage-based pricing
Used heavily by Pricing, Billing, and Subscription Management
Supports advanced tier and block pricing functionality
Linked with Fulfillment Lines and Pricing Adjustment tables

Primary Foreign Keys

Column	References Table
FULFILL_LINE_ID	DOO_FULFILL_LINES_ALL
PARENT_ENTITY_ID	DOO_FULFILL_LINES_ALL / DOO_LINES_ALL
ORDER_CHARGE_ID	DOO_ORDER_CHARGE_TIERS
PROMOTION_ITEM_TERM_ID	QP_PROMOTION_TERMS_B

Common Tables Used for Joins

Table	Join Condition
DOO_FULFILL_LINES_ALL	DOO_ORDER_CHARGES.PARENT_ENTITY_ID = DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID
DOO_LINES_ALL	DOO_ORDER_CHARGES.PARENT_ENTITY_ID = DOO_LINES_ALL.LINE_ID
DOO_HEADERS_ALL	DOO_LINES_ALL.HEADER_ID = DOO_HEADERS_ALL.HEADER_ID
DOO_ORDER_CHARGE_TIERS	DOO_ORDER_CHARGES.ORDER_CHARGE_ID = DOO_ORDER_CHARGE_TIERS.ORDER_CHARGE_ID
DOO_PRICE_ADJUSTMENTS	DOO_ORDER_CHARGES.ORDER_CHARGE_ID = DOO_PRICE_ADJUSTMENTS.ORDER_CHARGE_ID
QP_PRICE_LISTS_B	DOO_ORDER_CHARGES.CHARGE_CURRENCY_CODE = QP_PRICE_LISTS_B.CURRENCY_CODE

Important Flow


DOO_HEADERS_ALL
        ↓
DOO_LINES_ALL
        ↓
DOO_FULFILL_LINES_ALL
        ↓
DOO_ORDER_CHARGES
        ↓
DOO_ORDER_CHARGE_TIERS
        ↓
AR_INVOICE_LINES_ALL

Example Query

SELECT
    order_charge_id,
    charge_definition_code,
    charge_type_code,
    charge_currency_code,
    avg_unit_selling_price
FROM doo_order_charges
WHERE primary_flag = 'Y';

DOO_HEADERS_ALL (Sales Order Headers Table)

DOO_HOLDS_ALL stores hold definitions and hold application details for sales orders in Oracle Fusion Order Management. It contains order hold information, hold codes, hold reasons, hold status, release details, customer hold tracking, fulfillment restrictions, and orchestration processing controls.

Core Identifiers

Column	Description
HOLD_ID	Primary Key for Hold Record
HEADER_ID	Sales Order Header Identifier
LINE_ID	Sales Order Line Identifier
FULFILL_LINE_ID	Fulfillment Line Identifier
HOLD_INSTANCE_ID	Hold Instance Identifier
HOLD_CODE	Hold Code
HOLD_NAME	Hold Name
HOLD_TYPE_CODE	Hold Type

Hold Status Information

Column	Description
STATUS_CODE	Hold Status
ACTIVE_FLAG	Active Hold Indicator
RELEASED_FLAG	Released Hold Indicator
AUTOMATIC_FLAG	Automatically Applied Hold
MANUAL_FLAG	Manually Applied Hold
APPLY_HOLD_FLAG	Apply Hold Indicator
RELEASE_HOLD_FLAG	Release Hold Indicator

Hold Reason Information

Column	Description
HOLD_REASON_CODE	Hold Reason Code
HOLD_COMMENTS	Hold Comments
RELEASE_REASON_CODE	Release Reason Code
RELEASE_COMMENTS	Release Comments

Order & Customer Information

Column	Description
ORDER_NUMBER	Sales Order Number
SOURCE_ORDER_NUMBER	Source Order Number
SOURCE_ORDER_SYSTEM	Source Order System
SOLD_TO_CUSTOMER_ID	Sold To Customer Identifier
SOLD_TO_PARTY_ID	Sold To Party Identifier
CUSTOMER_PO_NUMBER	Customer Purchase Order Number

Processing & Fulfillment Information

Column	Description
FULFILLMENT_STATUS_CODE	Fulfillment Status
ORCHESTRATION_APPLICATION_ID	Orchestration Application Identifier
TASK_INSTANCE_ID	Orchestration Task Instance
PROCESS_INSTANCE_ID	Orchestration Process Instance
WAIT_FOR_APPROVAL_FLAG	Waiting for Approval Indicator
CREDIT_CHECK_FLAG	Credit Check Hold Indicator
SHIPPING_HOLD_FLAG	Shipping Hold Indicator
BILLING_HOLD_FLAG	Billing Hold Indicator

Date Information

Column	Description
HOLD_DATE	Date Hold Applied
RELEASE_DATE	Date Hold Released
REQUEST_DATE	Hold Request Date
CREATION_DATE	Record Creation Date
LAST_UPDATE_DATE	Last Updated Date

Control & System Information

Column	Description
OBJECT_VERSION_NUMBER	Optimistic Lock Version
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Audit Columns

Column	Description
CREATED_BY	Created By User
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Order Hold table in Oracle Fusion Order Management
Stores both manual and system-generated holds
Supports credit check, shipping, billing, and fulfillment holds
Holds can be applied at Header, Line, or Fulfillment Line level
Used heavily in Order Management workflow and orchestration processing
Important table for blocked order analysis and operational reporting

Primary Foreign Keys

Column	References Table
HEADER_ID	DOO_HEADERS_ALL
LINE_ID	DOO_LINES_ALL
FULFILL_LINE_ID	DOO_FULFILL_LINES_ALL
SOLD_TO_PARTY_ID	HZ_PARTIES
SOLD_TO_CUSTOMER_ID	HZ_CUST_ACCOUNTS
TASK_INSTANCE_ID	DOO_TASK_INSTANCES
PROCESS_INSTANCE_ID	DOO_PROCESS_INSTANCES

Common Tables Used for Joins

Table	Join Condition
DOO_HEADERS_ALL	DOO_HOLDS_ALL.HEADER_ID = DOO_HEADERS_ALL.HEADER_ID
DOO_LINES_ALL	DOO_HOLDS_ALL.LINE_ID = DOO_LINES_ALL.LINE_ID
DOO_FULFILL_LINES_ALL	DOO_HOLDS_ALL.FULFILL_LINE_ID = DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID
HZ_PARTIES	DOO_HOLDS_ALL.SOLD_TO_PARTY_ID = HZ_PARTIES.PARTY_ID
DOO_HOLD_INSTANCES	DOO_HOLDS_ALL.HOLD_INSTANCE_ID = DOO_HOLD_INSTANCES.HOLD_INSTANCE_ID
DOO_ORDER_ADDRESSES	DOO_HOLDS_ALL.HEADER_ID = DOO_ORDER_ADDRESSES.HEADER_ID
DOO_PAYMENTS	DOO_HOLDS_ALL.HEADER_ID = DOO_PAYMENTS.HEADER_ID

Important Flow


DOO_HEADERS_ALL
        ↓
DOO_LINES_ALL
        ↓
DOO_FULFILL_LINES_ALL
        ↓
DOO_HOLDS_ALL
        ↓
DOO_HOLD_INSTANCES

Example Query

SELECT
    dha.order_number,
    dha.hold_code,
    dha.hold_reason_code,
    dha.status_code,
    dha.hold_date
FROM doo_holds_all dha
WHERE dha.active_flag = 'Y';

HZ_CUST_ACCOUNTS (Customer Accounts Table)

HZ_CUST_ACCOUNTS stores customer account information in Oracle Fusion Trading Community Architecture (TCA). It contains customer account details, account status, customer classifications, tax information, billing controls, account relationships, and customer references used across Receivables, Order Management, Contracts, and Collections.

Core Identifiers

Column	Description
CUST_ACCOUNT_ID	Primary Key for Customer Account
PARTY_ID	Reference to Party Record
ACCOUNT_NUMBER	Customer Account Number
ACCOUNT_NAME	Customer Account Name
ORIG_SYSTEM_REFERENCE	Legacy System Customer Reference
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Customer Classification Information

Column	Description
CUSTOMER_TYPE	Internal or External Customer Type
CUSTOMER_CLASS_CODE	Customer Classification
STATUS	Customer Account Status
STATUS_UPDATE_DATE	Status Update Date
SELLING_PARTY_ID	Selling Organization Party

Tax & Billing Information

Column	Description
TAX_CODE	Customer Tax Code
TAX_HEADER_LEVEL_FLAG	Tax Header Level Indicator
TAX_ROUNDING_RULE	Tax Rounding Rule
HOLD_BILL_FLAG	Billing Hold Indicator
HELD_BILL_EXPIRATION_DATE	Billing Hold Expiration Date
DEPOSIT_REFUND_METHOD	Refund Method
AUTOPAY_FLAG	Auto Pay Indicator

Account Lifecycle Information

Column	Description
ACCOUNT_ESTABLISHED_DATE	Account Establishment Date
ACCOUNT_TERMINATION_DATE	Account Termination Date
DATE_TYPE_PREFERENCE	Ship or Arrival Date Preference
COTERMINATE_DAY_MONTH	Service Cotermination Date

Customer Reference Information

Column	Description
NPA_NUMBER	Telecommunication NPA Number
SOURCE_CODE	Customer Source or Promotion
COMMENTS	Customer Comments
ARRIVALSETS_INCLUDE_LINES_FLAG	Arrival Set Inclusion Indicator
LAST_BATCH_ID	Last Processing Batch Identifier

ESS & Processing Information

Column	Description
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name
CREATED_BY_MODULE	Module which Created Record
CONFLICT_ID	Mobile Conflict Identifier
USER_LAST_UPDATE_DATE	User Last Update Date

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Customer Account table in Oracle Fusion TCA
Stores customer account level information
Party details are stored separately in HZ_PARTIES
Used across Receivables, Order Management, Collections, and Contracts
Supports both internal and external customers
Central customer table for Order-to-Cash processing

Primary Foreign Keys

Column	References Table
PARTY_ID	HZ_PARTIES
SELLING_PARTY_ID	HZ_PARTIES
CUST_ACCOUNT_ID	HZ_CUST_ACCT_SITES_ALL
CUST_ACCOUNT_ID	HZ_CUSTOMER_PROFILES_F
CUST_ACCOUNT_ID	AR_PAYMENT_SCHEDULES_ALL
CUST_ACCOUNT_ID	AR_CASH_RECEIPTS_ALL
CUST_ACCOUNT_ID	RA_CUSTOMER_TRX_ALL

Common Tables Used for Joins

Table	Join Condition
HZ_PARTIES	HZ_CUST_ACCOUNTS.PARTY_ID = HZ_PARTIES.PARTY_ID
HZ_CUST_ACCT_SITES_ALL	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID
HZ_CUST_SITE_USES_ALL	HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID
RA_CUSTOMER_TRX_ALL	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID
AR_PAYMENT_SCHEDULES_ALL	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_ID
AR_CASH_RECEIPTS_ALL	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER
DOO_HEADERS_ALL	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = DOO_HEADERS_ALL.SOLD_TO_CUSTOMER_ID
RA_CUSTOMER_TRX_LINES_ALL	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = RA_CUSTOMER_TRX_LINES_ALL.SHIP_TO_CUSTOMER_ID

Important Flow


HZ_PARTIES
        ↓
HZ_CUST_ACCOUNTS
        ↓
HZ_CUST_ACCT_SITES_ALL
        ↓
HZ_CUST_SITE_USES_ALL
        ↓
DOO_HEADERS_ALL
        ↓
RA_CUSTOMER_TRX_ALL
        ↓
AR_CASH_RECEIPTS_ALL

Example Query

SELECT
    hca.account_number,
    hca.account_name,
    hp.party_name,
    hca.customer_class_code,
    hca.status
FROM hz_cust_accounts hca
JOIN hz_parties hp
    ON hca.party_id = hp.party_id
WHERE hca.status = 'A';

HZ_PARTIES (Parties / Suppliers / Organizations Table)

HZ_PARTIES is the master party table in Oracle Fusion Trading Community Architecture (TCA). It stores information about organizations, suppliers, customers, persons, groups, and business entities. For Supplier Management, this table acts as the master organization table linked with POZ_SUPPLIERS and supplier-related transactions.

Core Identifiers

Column	Description
PARTY_ID	Primary Key for Party
PARTY_NUMBER	Unique Party Number
PARTY_NAME	Supplier / Organization / Person Name
PARTY_UNIQUE_NAME	Derived Unique Party Name
PARTY_TYPE	Person / Organization / Group
STATUS	Party Status
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Supplier & Organization Information

Column	Description
ORIG_SYSTEM_REFERENCE	Legacy System Reference
SIC_CODE	Industry Classification Code
SIC_CODE_TYPE	SIC Version Type
YEAR_ESTABLISHED	Organization Establishment Year
MISSION_STATEMENT	Organization Mission Statement
VALIDATED_FLAG	Validation Status
GSA_INDICATOR_FLAG	US Federal GSA Indicator
CATEGORY_CODE	Customer / Supplier Category
THIRD_PARTY_FLAG	Third Party Relationship Indicator
CERTIFICATION_LEVEL	Supplier Certification Level
CERT_REASON_CODE	Certification Reason
TRADING_PARTNER_IDENTIFIER	Trading Partner Identifier
SALES_ACCOUNT_ID	Sales Account Identifier
MASTER_PARTY_ID	Master Party Reference

Address Information

Column	Description
COUNTRY	Country Code
ADDRESS1	Address Line 1
ADDRESS2	Address Line 2
ADDRESS3	Address Line 3
ADDRESS4	Address Line 4
CITY	City
STATE	State
PROVINCE	Province
COUNTY	County
POSTAL_CODE	Postal Code
IDEN_ADDR_PARTY_SITE_ID	Identifying Party Site
IDEN_ADDR_LOCATION_ID	Identifying Location

Contact Information

Column	Description
EMAIL_ADDRESS	Email Address
URL	Website URL
PRIMARY_PHONE_COUNTRY_CODE	Phone Country Code
PRIMARY_PHONE_AREA_CODE	Phone Area Code
PRIMARY_PHONE_NUMBER	Primary Phone Number
PRIMARY_PHONE_EXTENSION	Phone Extension
PRIMARY_PHONE_PURPOSE	Phone Purpose
PREFERRED_CONTACT_METHOD	Preferred Contact Method
PRIMARY_EMAIL_CONTACT_PT_ID	Primary Email Contact Point
PRIMARY_URL_CONTACT_PT_ID	Primary URL Contact Point

Person Information

Column	Description
PERSON_FIRST_NAME	First Name
PERSON_MIDDLE_NAME	Middle Name
PERSON_LAST_NAME	Last Name
PERSON_SECOND_LAST_NAME	Second Last Name
PERSON_TITLE	Person Title
PERSON_ACADEMIC_TITLE	Academic Title
DATE_OF_BIRTH	Date of Birth
GENDER	Gender
MARITAL_STATUS	Marital Status
SALUTATION	Salutation

Financial & Revenue Information

Column	Description
CURR_FY_POTENTIAL_REVENUE	Current Fiscal Year Revenue
NEXT_FY_POTENTIAL_REVENUE	Next Fiscal Year Revenue
PREF_FUNCTIONAL_CURRENCY	Preferred Functional Currency
CEO_NAME	CEO Name
EMPLOYEES_TOTAL	Total Employees
ANALYSIS_FY	Analysis Fiscal Year
FISCAL_YEAREND_MONTH	Fiscal Year End Month

ESS & Processing Information

Column	Description
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name
CREATED_BY_MODULE	Module which Created Record
USER_GUID	User Global Identifier
CONFLICT_ID	Mobile Conflict Identifier
USER_LAST_UPDATE_DATE	User Last Update Date

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Party Master table in Oracle Fusion TCA
Stores Customers, Suppliers, Organizations, Persons, and Groups
Supplier records are linked through POZ_SUPPLIERS
Customer accounts are linked through HZ_CUST_ACCOUNTS
Used across Procurement, Receivables, Payables, SCM, and CRM
Central master entity table in Oracle Fusion Applications

Primary Foreign Keys

Column	References Table
PARTY_ID	POZ_SUPPLIERS
PARTY_ID	HZ_CUST_ACCOUNTS
PARTY_ID	HZ_PARTY_SITES
PARTY_ID	HZ_ORGANIZATION_PROFILES
PARTY_ID	HZ_PERSON_PROFILES
PARTY_ID	ZX_PARTY_TAX_PROFILE
PARTY_ID	XLE_ENTITY_PROFILES

Common Tables Used for Joins

Table	Join Condition
POZ_SUPPLIERS	HZ_PARTIES.PARTY_ID = POZ_SUPPLIERS.PARTY_ID
HZ_CUST_ACCOUNTS	HZ_PARTIES.PARTY_ID = HZ_CUST_ACCOUNTS.PARTY_ID
HZ_PARTY_SITES	HZ_PARTIES.PARTY_ID = HZ_PARTY_SITES.PARTY_ID
HZ_LOCATIONS	HZ_PARTY_SITES.LOCATION_ID = HZ_LOCATIONS.LOCATION_ID
POZ_SUPPLIER_SITES_ALL_M	POZ_SUPPLIERS.VENDOR_ID = POZ_SUPPLIER_SITES_ALL_M.VENDOR_ID
AP_INVOICES_ALL	HZ_PARTIES.PARTY_ID = AP_INVOICES_ALL.PARTY_ID
DOO_HEADERS_ALL	HZ_PARTIES.PARTY_ID = DOO_HEADERS_ALL.SOLD_TO_PARTY_ID
RA_CUSTOMER_TRX_ALL	HZ_PARTIES.PARTY_ID = RA_CUSTOMER_TRX_ALL.SOLD_TO_PARTY_ID

Important Flow


HZ_PARTIES
        ↓
POZ_SUPPLIERS
        ↓
POZ_SUPPLIER_SITES_ALL_M
        ↓
PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
AP_INVOICES_ALL

Example Query

SELECT
    hp.party_name,
    hp.party_number,
    hp.party_type,
    hp.email_address,
    ps.segment1 supplier_number
FROM hz_parties hp
JOIN poz_suppliers ps
    ON hp.party_id = ps.party_id
WHERE hp.status = 'A';

HZ_CUST_SITE_USES_ALL (Customer Account Site Uses Table)

HZ_CUST_SITE_USES_ALL stores customer account site usage information in Oracle Fusion Trading Community Architecture (TCA). It defines business purposes for customer account sites such as Bill-To, Ship-To, Sold-To, Statements, and Market sites. This table is heavily used across Receivables, Order Management, Tax, Collections, and Contracts.

Core Identifiers

Column	Description
SITE_USE_ID	Primary Key for Customer Site Use
CUST_ACCT_SITE_ID	Customer Account Site Identifier
SITE_USE_CODE	Business Purpose of Site
LOCATION	Site Location Name
STATUS	Site Use Status
PRIMARY_FLAG	Primary Site Indicator
SET_ID	Reference Data Set Identifier
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Customer Site Use Information

Column	Description
BILL_TO_SITE_USE_ID	Related Bill-To Site Use
ORIG_SYSTEM_REFERENCE	Legacy System Site Reference
SIC_CODE	Industry Classification Code
GSA_INDICATOR	US Federal GSA Indicator
TERRITORY_ID	Territory Identifier
SORT_PRIORITY	Site Use Priority
START_DATE	Valid From Date
END_DATE	Valid To Date

Tax & Billing Information

Column	Description
PAYMENT_TERM_ID	Payment Terms Identifier
TAX_REFERENCE	Taxpayer Reference Number
TAX_CODE	Tax Code
TAX_CLASSIFICATION	Tax Classification
TAX_HEADER_LEVEL_FLAG	Header Level Tax Indicator
TAX_ROUNDING_RULE	Tax Rounding Rule
FINCHRG_RECEIVABLES_TRX_ID	Finance Charge Transaction Type

Finance Charge Information

Column	Description
LAST_ACCRUE_CHARGE_DATE	Last Finance Charge Accrual Date
SECOND_LAST_ACCRUE_CHARGE_DATE	Previous Finance Charge Accrual Date
LAST_UNACCRUE_CHARGE_DATE	Last Finance Charge Reversal Date
SECOND_LAST_UNACCRUE_CHRG_DATE	Previous Finance Charge Reversal Date

ESS & Processing Information

Column	Description
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name
CREATED_BY_MODULE	Module which Created Record
CONFLICT_ID	Mobile Conflict Identifier
USER_LAST_UPDATE_DATE	User Last Update Date

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Stores Customer Account Site business purposes
Defines Bill-To, Ship-To, Sold-To, and Statement Sites
Used heavily in Receivables and Order Management transactions
Links Customer Sites with Billing and Shipping operations
Central table for Customer Site usage setup
Supports tax, collections, and finance charge processing

Primary Foreign Keys

Column	References Table
CUST_ACCT_SITE_ID	HZ_CUST_ACCT_SITES_ALL
BILL_TO_SITE_USE_ID	HZ_CUST_SITE_USES_ALL
PAYMENT_TERM_ID	RA_TERMS_B
SITE_USE_ID	RA_CUSTOMER_TRX_ALL
SITE_USE_ID	AR_PAYMENT_SCHEDULES_ALL
SITE_USE_ID	AR_CASH_RECEIPTS_ALL
SITE_USE_ID	ZX_LINES_DET_FACTORS

Common Tables Used for Joins

Table	Join Condition
HZ_CUST_ACCT_SITES_ALL	HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID
HZ_CUST_ACCOUNTS	HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
HZ_PARTY_SITES	HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID
RA_CUSTOMER_TRX_ALL	HZ_CUST_SITE_USES_ALL.SITE_USE_ID = RA_CUSTOMER_TRX_ALL.BILL_TO_SITE_USE_ID
AR_PAYMENT_SCHEDULES_ALL	HZ_CUST_SITE_USES_ALL.SITE_USE_ID = AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_SITE_USE_ID
AR_CASH_RECEIPTS_ALL	HZ_CUST_SITE_USES_ALL.SITE_USE_ID = AR_CASH_RECEIPTS_ALL.CUSTOMER_SITE_USE_ID
DOO_FULFILL_LINES_ALL	HZ_CUST_SITE_USES_ALL.SITE_USE_ID = DOO_FULFILL_LINES_ALL.BILL_TO_SITE_USE_ID
ZX_LINES_DET_FACTORS	HZ_CUST_SITE_USES_ALL.SITE_USE_ID = ZX_LINES_DET_FACTORS.BILL_TO_CUST_ACCT_SITE_USE_ID

Important Flow


HZ_PARTIES
        ↓
HZ_CUST_ACCOUNTS
        ↓
HZ_CUST_ACCT_SITES_ALL
        ↓
HZ_CUST_SITE_USES_ALL
        ↓
DOO_FULFILL_LINES_ALL
        ↓
RA_CUSTOMER_TRX_ALL
        ↓
AR_CASH_RECEIPTS_ALL

Example Query

SELECT
    hcsu.site_use_id,
    hcsu.site_use_code,
    hcsu.location,
    hcsu.status,
    hca.account_number
FROM hz_cust_site_uses_all hcsu
JOIN hz_cust_acct_sites_all hcasa
    ON hcsu.cust_acct_site_id = hcasa.cust_acct_site_id
JOIN hz_cust_accounts hca
    ON hcasa.cust_account_id = hca.cust_account_id
WHERE hcsu.status = 'A';

Receiving

RCV_TRANSACTIONS (Receiving Transactions Table)

RCV_TRANSACTIONS stores receiving transaction details in Oracle Fusion Receiving. It contains receipt, delivery, inspection, return-to-supplier, correction, inventory delivery, consigned inventory consumption, transfer order receipts, and put away transactions. This is one of the most important SCM execution tables used across Procurement, Inventory, Costing, Payables, Receiving, Quality, and Supply Chain execution.

Core Identifiers

Column	Description
TRANSACTION_ID	Primary Key for Receiving Transaction
TRANSACTION_TYPE	Receiving Transaction Type
TRANSACTION_DATE	Receiving Transaction Date
PARENT_TRANSACTION_ID	Parent Receiving Transaction
SOURCE_TRANSACTION_NUM	Source Transaction Reference Number
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Quantity & UOM Information

Column	Description
QUANTITY	Transaction Quantity
PRIMARY_QUANTITY	Primary UOM Quantity
SECONDARY_QUANTITY	Secondary UOM Quantity
PRICING_QUANTITY	Pricing Quantity
CONSUMED_QUANTITY	Consigned Inventory Consumed Quantity
QUANTITY_BILLED	Billed Quantity
AMOUNT	Transaction Amount
AMOUNT_BILLED	Billed Amount
UOM_CODE	Transaction UOM
PRIMARY_UOM_CODE	Primary UOM
SECONDARY_UOM_CODE	Secondary UOM
PRICING_UOM_CODE	Pricing UOM
BILLING_UOM_CODE	Billing UOM
SP_UOM_CODE	Standard Pack UOM

Purchase Order Information

Column	Description
PO_HEADER_ID	Purchase Order Header Identifier
PO_LINE_ID	Purchase Order Line Identifier
PO_LINE_LOCATION_ID	Purchase Order Shipment Schedule
PO_DISTRIBUTION_ID	Purchase Order Distribution
PO_REVISION_NUM	Purchase Order Revision Number
PO_UNIT_PRICE	Purchase Order Unit Price
CURRENCY_CODE	PO Currency Code
CURRENCY_CONVERSION_TYPE	Currency Conversion Type
CURRENCY_CONVERSION_RATE	Currency Conversion Rate
CURRENCY_CONVERSION_DATE	Currency Conversion Date

Receipt & Shipment Information

Column	Description
SHIPMENT_HEADER_ID	Receipt Shipment Header
SHIPMENT_LINE_ID	Receipt Shipment Line
ROUTING_HEADER_ID	Receiving Routing
ROUTING_STEP_ID	Receiving Routing Step
DELIVERY_REQUIRED_FLAG	Delivery Required Indicator
USER_ENTERED_FLAG	User Entered Transaction Indicator
INTERFACE_SOURCE_CODE	Source Interface Code
INTERFACE_TRANSACTION_ID	Interface Transaction Identifier
GROUP_ID	Interface Group Identifier

Supplier Information

Column	Description
VENDOR_ID	Supplier Identifier
VENDOR_SITE_ID	Supplier Site Identifier
VENDOR_LOT_NUM	Supplier Lot Number
RMA_REFERENCE	Supplier RMA Reference
SUPPLIER_RETURN_HEADER_ID	Supplier Return Header
SUPPLIER_RETURN_LINE_ID	Supplier Return Line
RETURN_FOR_CREDIT_FLAG	Return for Credit Indicator

Inventory Information

Column	Description
ORGANIZATION_ID	Receiving Organization
SUBINVENTORY	Destination Subinventory
LOCATOR_ID	Destination Locator
FROM_SUBINVENTORY	Source Subinventory
FROM_LOCATOR_ID	Source Locator
INV_TRANSACTION_ID	Inventory Transaction Identifier
LPN_ID	License Plate Number
TRANSFER_LPN_ID	Transfer License Plate Number
CONSIGNED_FLAG	Consigned Material Indicator
BACK_TO_BACK_FLAG	Back-to-Back Order Indicator
DIRECT_TRANSFER_ORDER_FLAG	Direct Transfer Order Indicator

Inspection & Quality Information

Column	Description
INSPECTION_STATUS_CODE	Inspection Status
INSPECTION_QUALITY_CODE	Inspection Quality Code
RECEIPT_EXCEPTION_FLAG	Receipt Exception Indicator
QA_COLLECTION_ID	Quality Collection Identifier
QA_IP_EVENT_ID	Quality Inspection Event
QA_SKIP_INSPECTION	Skip Inspection Indicator
QC_GRADE	Quality Grade

Receiving Status Information

Column	Description
ACCRUAL_STATUS_CODE	Costing Interface Status
INVOICE_STATUS_CODE	Invoice Matching Status
MATCH_FLAG	Invoice Match Required
MATCH_OPTION	PO or Receipt Matching Option
RCPT_CONF_STATUS	Receipt Confirmation Status
BUSINESS_EVENT_PUBLISHED	Business Event Published Indicator
CSE_INTERFACE_STATUS_CODE	ALM Interface Status
ALM_INTERFACE_STATUS	Genealogy Interface Status
SCH_INTERFACE_STATUS	Recall Management Interface Status

Transfer Order Information

Column	Description
TRANSFER_ORDER_HEADER_ID	Transfer Order Header
TRANSFER_ORDER_LINE_ID	Transfer Order Line
TRANSFER_ORDER_DIST_ID	Transfer Order Distribution
PHYSICAL_RETURN_REQD	Physical Return Required
REPLENISH_ORDER_LINE_ID	Replenishment Sales Order Line

Project & Task Information

Column	Description
PROJECT_ID	Project Identifier
TASK_ID	Task Identifier
PA_ADDITION_FLAG	Transferred to Oracle Projects

Tax Information

Column	Description
DEFAULT_TAXATION_COUNTRY	Default Tax Country
TAX_CLASSIFICATION_CODE	Tax Classification Code
TAX_INVOICE_NUMBER	Tax Invoice Number
TAX_INVOICE_DATE	Tax Invoice Date
DOCUMENT_FISCAL_CLASSIFICATION	Fiscal Classification
TRX_BUSINESS_CATEGORY	Transaction Business Category
PRODUCT_TYPE	Product Type
PRODUCT_CATEGORY	Product Category
ASSESSABLE_VALUE	Assessable Tax Value
INTENDED_USE	Intended Use

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Important Notes

Main Receiving execution table in Oracle Fusion SCM
Stores Receipt, Delivery, Return, Inspection, and Correction transactions
Central table for Procure-to-Pay receiving lifecycle
Used heavily in Receiving, Inventory, Costing, and Payables
Links Purchasing documents with Inventory transactions
Supports Consigned Inventory, Transfer Orders, and Back-to-Back flows

Primary Foreign Keys

Column	References Table
PO_HEADER_ID	PO_HEADERS_ALL
PO_LINE_ID	PO_LINES_ALL
PO_LINE_LOCATION_ID	PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTION_ID	PO_DISTRIBUTIONS_ALL
REQUISITION_LINE_ID	POR_REQUISITION_LINES_ALL
REQ_DISTRIBUTION_ID	POR_REQ_DISTRIBUTIONS_ALL
SHIPMENT_HEADER_ID	RCV_SHIPMENT_HEADERS
SHIPMENT_LINE_ID	RCV_SHIPMENT_LINES
VENDOR_ID	POZ_SUPPLIERS
VENDOR_SITE_ID	POZ_SUPPLIER_SITES_ALL_M
INV_TRANSACTION_ID	INV_MATERIAL_TXNS
LOCATOR_ID	INV_ITEM_LOCATIONS
SUBINVENTORY, ORGANIZATION_ID	INV_SECONDARY_INVENTORIES

Common Tables Used for Joins

Table	Join Condition
PO_HEADERS_ALL	RCV_TRANSACTIONS.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
PO_LINES_ALL	RCV_TRANSACTIONS.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
PO_LINE_LOCATIONS_ALL	RCV_TRANSACTIONS.PO_LINE_LOCATION_ID = PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
PO_DISTRIBUTIONS_ALL	RCV_TRANSACTIONS.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
RCV_SHIPMENT_HEADERS	RCV_TRANSACTIONS.SHIPMENT_HEADER_ID = RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
RCV_SHIPMENT_LINES	RCV_TRANSACTIONS.SHIPMENT_LINE_ID = RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID
INV_MATERIAL_TXNS	RCV_TRANSACTIONS.INV_TRANSACTION_ID = INV_MATERIAL_TXNS.TRANSACTION_ID
AP_INVOICE_LINES_ALL	RCV_TRANSACTIONS.TRANSACTION_ID = AP_INVOICE_LINES_ALL.RCV_TRANSACTION_ID
POZ_SUPPLIERS	RCV_TRANSACTIONS.VENDOR_ID = POZ_SUPPLIERS.VENDOR_ID

Important Flow


PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
PO_LINE_LOCATIONS_ALL
        ↓
PO_DISTRIBUTIONS_ALL
        ↓
RCV_SHIPMENT_LINES
        ↓
RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS
        ↓
AP_INVOICE_LINES_ALL

Example Query

SELECT
    rt.transaction_id,
    rt.transaction_type,
    rt.transaction_date,
    rt.quantity,
    rt.uom_code,
    rt.accrual_status_code
FROM rcv_transactions rt
WHERE rt.transaction_type = 'RECEIVE';

RCV_SHIPMENT_HEADERS (Receiving Shipment Headers Table)

RCV_SHIPMENT_HEADERS stores receipt header and shipment header information in Oracle Fusion Receiving. It contains ASN (Advance Shipment Notice), ASBN (Advance Shipment Billing Notice), supplier shipment details, transfer order shipment data, customer return receipt information, freight details, logistics data, and receiving header level information.

Core Identifiers

Column	Description
SHIPMENT_HEADER_ID	Primary Key for Shipment Header
SHIPMENT_NUM	Supplier / Transfer Shipment Number
RECEIPT_NUM	Receipt Number
RECEIPT_SOURCE_CODE	Receipt Source Type
ASN_TYPE	ASN / ASBN / Standard Receipt Type
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Supplier Information

Column	Description
VENDOR_ID	Supplier Identifier
VENDOR_SITE_ID	Supplier Site Identifier
SUPPLIER_SHIPMENT_NUMBER	Supplier Shipment Number
SHIP_FROM_LOCATION_ID	Supplier Ship From Location
REMIT_TO_SITE_ID	Supplier Remit To Site
RA_OUTSOURCER_PARTY_ID	Logistics Service Provider Party
RA_OUTSOURCER_CONTACT_ID	Logistics Service Provider Contact

Shipment & Receiving Information

Column	Description
SHIP_TO_LOCATION_ID	Receiving Location
SHIP_TO_ORG_ID	Receiving Inventory Organization
ORGANIZATION_ID	Source Organization
SHIPPED_DATE	Shipment Creation Date
EXPECTED_RECEIPT_DATE	Expected Receipt Date
REQUEST_DATE	Requested Receipt Date
GL_DATE	General Ledger Date
NUM_OF_CONTAINERS	Number of Containers
CONTAINER_NUMBER	Container Number
COMMENTS	Receiver Comments

Shipping & Freight Information

Column	Description
FREIGHT_CARRIER_ID	Freight Carrier Identifier
BILL_OF_LADING	Bill of Lading Number
PACKING_SLIP	Packing Slip Number
WAYBILL_AIRBILL_NUM	Waybill / Airbill Number
SHIP_METHOD_CODE	Shipping Method
MODE_OF_TRANSPORT	Mode of Transport
SERVICE_LEVEL	Shipping Service Level
CARRIER_METHOD	Carrier Transportation Method
CARRIER_EQUIPMENT	Carrier Equipment Description
FREIGHT_TERMS	Freight Terms
FREIGHT_BILL_NUMBER	Freight Bill Number
FREIGHT_AMOUNT	Freight Amount

Weight & Packaging Information

Column	Description
GROSS_WEIGHT	Gross Weight
GROSS_WEIGHT_UOM_CODE	Gross Weight UOM
NET_WEIGHT	Net Weight
NET_WEIGHT_UOM_CODE	Net Weight UOM
TAR_WEIGHT	Tare Weight
TAR_WEIGHT_UOM_CODE	Tare Weight UOM
PACKAGING_CODE	Packaging Code
SPECIAL_HANDLING_CODE	Special Handling Code

Hazardous Material Information

Column	Description
HAZARD_CODE	Hazardous Material Indicator
HAZARD_CLASS	Hazardous Material Class
HAZARD_DESCRIPTION	Hazardous Material Description

Invoice & Billing Information

Column	Description
INVOICE_NUM	Supplier Invoice Number
INVOICE_DATE	Supplier Invoice Date
INVOICE_AMOUNT	Invoice Amount
TAX_NAME	Tax Name
TAX_AMOUNT	Tax Amount
INVOICE_STATUS_CODE	Invoice Processing Status
CURRENCY_CODE	Invoice Currency
CONVERSION_RATE_TYPE	Currency Conversion Type
CONVERSION_RATE	Currency Conversion Rate
CONVERSION_DATE	Currency Conversion Date
PAYMENT_TERMS_ID	Payment Terms Identifier

Customer & RMA Information

Column	Description
CUSTOMER_ID	Customer Identifier
CUSTOMER_SITE_ID	Customer Site Identifier
RMA_BU_ID	RMA Business Unit
RECEIPT_ADVICE_NUMBER	Receipt Advice Number
RA_DOCUMENT_CODE	Receipt Advice Source Document Type
RA_DOCUMENT_NUMBER	Receipt Advice Document Number
RA_DOC_REVISION_NUMBER	Receipt Advice Revision Number

EDI & External Integration Information

Column	Description
EDI_CONTROL_NUM	EDI Transaction Control Number
NOTICE_CREATION_DATE	EDI Notice Creation Date
HEADER_INTERFACE_ID	Receipt Interface Identifier
EXTERNAL_SYS_TXN_REFERENCE	External WMS / 3PL Reference
RA_ORIG_SYSTEM_REF	Receipt Advice Source System
RA_DOO_SOURCE_SYSTEM_ID	DOO Source System Identifier

Status & Processing Information

Column	Description
APPROVAL_STATUS	Approval Status
ASN_STATUS	ASN Processing Status
LSP_FLAG	Logistics Service Provider Indicator

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Important Notes

Main Receipt Header table in Oracle Fusion Receiving
Stores ASN, ASBN, Transfer Shipment, and Receipt Header data
One Shipment Header can have multiple Shipment Lines
Used heavily in Receiving, Procurement, Inventory, and Payables
Central table for Receipt Number tracking
Supports Supplier Receipts, Customer RMAs, and Transfer Orders

Primary Foreign Keys

Column	References Table
SHIPMENT_HEADER_ID	RCV_SHIPMENT_LINES
SHIPMENT_HEADER_ID	RCV_TRANSACTIONS
VENDOR_ID	POZ_SUPPLIERS
VENDOR_SITE_ID	POZ_SUPPLIER_SITES_ALL_M
SHIP_TO_ORG_ID	INV_ORGANIZATION_DEFINITIONS_V
CUSTOMER_ID	HZ_CUST_ACCOUNTS
CUSTOMER_SITE_ID	HZ_CUST_SITE_USES_ALL
PAYMENT_TERMS_ID	RA_TERMS_B

Common Tables Used for Joins

Table	Join Condition
RCV_SHIPMENT_LINES	RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID = RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID
RCV_TRANSACTIONS	RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID = RCV_TRANSACTIONS.SHIPMENT_HEADER_ID
POZ_SUPPLIERS	RCV_SHIPMENT_HEADERS.VENDOR_ID = POZ_SUPPLIERS.VENDOR_ID
POZ_SUPPLIER_SITES_ALL_M	RCV_SHIPMENT_HEADERS.VENDOR_SITE_ID = POZ_SUPPLIER_SITES_ALL_M.VENDOR_SITE_ID
INV_ORGANIZATION_DEFINITIONS_V	RCV_SHIPMENT_HEADERS.SHIP_TO_ORG_ID = INV_ORGANIZATION_DEFINITIONS_V.ORGANIZATION_ID
HZ_CUST_ACCOUNTS	RCV_SHIPMENT_HEADERS.CUSTOMER_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
AP_INVOICES_ALL	RCV_SHIPMENT_HEADERS.INVOICE_NUM = AP_INVOICES_ALL.INVOICE_NUM

Important Flow


PO_HEADERS_ALL
        ↓
RCV_SHIPMENT_HEADERS
        ↓
RCV_SHIPMENT_LINES
        ↓
RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS
        ↓
AP_INVOICE_LINES_ALL

Example Query

SELECT
    receipt_num,
    shipment_num,
    receipt_source_code,
    expected_receipt_date,
    invoice_num
FROM rcv_shipment_headers
WHERE receipt_source_code = 'VENDOR';

RCV_SHIPMENT_LINES (Receiving Shipment Lines Table)

RCV_SHIPMENT_LINES stores shipment line and receipt line details in Oracle Fusion Receiving. It contains item shipment information, receipt quantities, delivery quantities, inspection quantities, supplier shipment details, purchase order references, routing information, transfer order data, tax determinants, consigned inventory details, and receiving execution information.

Core Identifiers

Column	Description
SHIPMENT_LINE_ID	Primary Key for Shipment Line
SHIPMENT_HEADER_ID	Shipment Header Identifier
LINE_NUM	Shipment Line Number
SOURCE_DOCUMENT_CODE	Source Document Type
SHIPMENT_LINE_STATUS_CODE	Shipment Line Status
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Item Information

Column	Description
ITEM_ID	Inventory Item Identifier
ITEM_DESCRIPTION	Inventory Item Description
ITEM_REVISION	Inventory Item Revision
CATEGORY_ID	Item Category Identifier
VENDOR_ITEM_NUM	Supplier Item Number
CUSTOMER_ITEM_NUM	Customer Item Number
CUSTOMER_ITEM_ID	Customer Item Identifier
COUNTRY_OF_ORIGIN_CODE	Country of Origin

Quantity Information

Column	Description
QUANTITY_SHIPPED	Quantity Shipped
QUANTITY_RECEIVED	Quantity Received
QUANTITY_DELIVERED	Quantity Delivered
QUANTITY_RETURNED	Quantity Returned
QUANTITY_ACCEPTED	Quantity Accepted
QUANTITY_REJECTED	Quantity Rejected
SECONDARY_QUANTITY_SHIPPED	Secondary Quantity Shipped
SECONDARY_QUANTITY_RECEIVED	Secondary Quantity Received
SECONDARY_QTY_DELIVERED	Secondary Quantity Delivered
SECONDARY_QTY_RETURNED	Secondary Quantity Returned
SECONDARY_QTY_ACCEPTED	Secondary Quantity Accepted
SECONDARY_QTY_REJECTED	Secondary Quantity Rejected
CONSUMED_QUANTITY	Consigned Inventory Consumed Quantity
RA_QUANTITY_EXPECTED	Receipt Advice Expected Quantity
RA_SECONDARY_QUANTITY_EXPECTED	Expected Secondary Quantity

UOM Information

Column	Description
UOM_CODE	Transaction UOM
PRIMARY_UOM_CODE	Primary UOM
SECONDARY_UOM_CODE	Secondary UOM
SP_UOM_CODE	Standard Pack UOM
SP_QUANTITY	Standard Pack Quantity
UOM_CONVERSION_RATE	UOM Conversion Rate

Purchase Order Information

Column	Description
PO_HEADER_ID	Purchase Order Header Identifier
PO_LINE_ID	Purchase Order Line Identifier
PO_LINE_LOCATION_ID	Purchase Order Shipment Schedule
PO_DISTRIBUTION_ID	Purchase Order Distribution
REQUISITION_LINE_ID	Requisition Line Identifier
REQ_DISTRIBUTION_ID	Requisition Distribution Identifier
SHIPMENT_UNIT_PRICE	Shipment Unit Price
NOTICE_UNIT_PRICE	ASN Unit Price

Inventory & Receiving Information

Column	Description
FROM_ORGANIZATION_ID	Source Organization
TO_ORGANIZATION_ID	Receiving Organization
TO_SUBINVENTORY	Destination Subinventory
LOCATOR_ID	Destination Locator
DELIVER_TO_LOCATION_ID	Deliver To Location
DELIVER_TO_PERSON_ID	Deliver To Person
DESTINATION_TYPE_CODE	Destination Type
ROUTING_HEADER_ID	Receiving Routing
MMT_TRANSACTION_ID	Inventory Transaction Identifier
INTERFACE_TRANSACTION_ID	Receiving Interface Transaction Identifier

Supplier & Shipment Information

Column	Description
VENDOR_LOT_NUM	Supplier Lot Number
PACKING_SLIP	Packing Slip Number
CONTAINER_NUM	Container Number
TRUCK_NUM	Truck Number
BAR_CODE_LABEL	Barcode Label
ASN_LINE_FLAG	ASN Shipment Line Indicator
ASN_LPN_ID	ASN License Plate Number
COMMENTS	Receiver Comments

Transfer Order & Supply Information

Column	Description
TRANSFER_ORDER_HEADER_ID	Transfer Order Header
TRANSFER_ORDER_LINE_ID	Transfer Order Line
TRANSFER_COST	Transfer Cost
TRANSPORTATION_COST	Transportation Cost
TRANSFER_PERCENTAGE	Transfer Cost Percentage
ORIG_TO_INV_SHIP_TXN_ID	Original Transfer Shipment Transaction
BACK_TO_BACK_FLAG	Back-to-Back Order Indicator
CONSIGNED_FLAG	Consigned Inventory Indicator
EXTERNAL_SYS_INTF_STATUS	External System Interface Status

Tax Information

Column	Description
TAX_NAME	Tax Name
TAX_AMOUNT	Tax Amount
DEFAULT_TAXATION_COUNTRY	Default Tax Country
TAX_INVOICE_NUMBER	Tax Invoice Number
TAX_INVOICE_DATE	Tax Invoice Date
TAX_CLASSIFICATION_CODE	Tax Classification Code
DOCUMENT_FISCAL_CLASSIFICATION	Fiscal Classification
TRX_BUSINESS_CATEGORY	Transaction Business Category
PRODUCT_TYPE	Product Type
PRODUCT_CATEGORY	Product Category
ASSESSABLE_VALUE	Assessable Value
INTENDED_USE	Intended Use

Project & Striping Information

Column	Description
PROJECT_ID	Project Identifier
TASK_ID	Task Identifier
INV_STRIPING_CATEGORY	Inventory Striping Category

RMA & Receipt Advice Information

Column	Description
RECEIPT_ADVICE_HEADER_ID	Receipt Advice Header
RECEIPT_ADVICE_LINE_ID	Receipt Advice Line
RECEIPT_ADVICE_LINE_NUMBER	Receipt Advice Line Number
RA_DOCUMENT_LINE_NUMBER	Receipt Advice Source Line Number
RA_DOO_HEADER_NUMBER	DOO Header Number
RA_DOO_LINE_NUMBER	DOO Line Number
RA_DOO_FULFILLMENT_LINE_NUMBER	DOO Fulfillment Line Number
CUSTOMER_ID	Customer Identifier
CUSTOMER_SITE_ID	Customer Site Identifier

Status & Processing Information

Column	Description
INVOICE_STATUS_CODE	Invoice Processing Status
APPROVAL_STATUS	Approval Status
CST_TRANSFER_STATUS	Costing Transfer Status
DS_LINE_INTERFACED_TO_OM	Dropship Interface to OM Status
ELASTIC_SEARCH_INGEST_STATUS	Elastic Search Ingestion Status
RA_LAST_ACTION_CODE	Last Receipt Advice Action

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Important Notes

Main Receipt Shipment Line table in Oracle Fusion Receiving
Stores item-level shipment and receipt details
Links Purchase Orders with Receiving Transactions
Used heavily in Receiving, Inventory, Costing, and Payables
Supports ASN, Transfer Orders, RMAs, and Consigned Inventory
One Shipment Header can contain multiple Shipment Lines

Primary Foreign Keys

Column	References Table
SHIPMENT_HEADER_ID	RCV_SHIPMENT_HEADERS
CATEGORY_ID	EGP_CATEGORIES_B
PO_HEADER_ID	PO_HEADERS_ALL
PO_LINE_ID	PO_LINES_ALL
PO_LINE_LOCATION_ID	PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTION_ID	PO_DISTRIBUTIONS_ALL
REQUISITION_LINE_ID	POR_REQUISITION_LINES_ALL
REQ_DISTRIBUTION_ID	POR_REQ_DISTRIBUTIONS_ALL
SHIPMENT_LINE_ID	RCV_TRANSACTIONS
TO_SUBINVENTORY, TO_ORGANIZATION_ID	INV_SECONDARY_INVENTORIES
REASON_ID	INV_TRANSACTION_REASONS_B
MMT_TRANSACTION_ID	INV_MATERIAL_TXNS
TRANSFER_ORDER_HEADER_ID	INV_TRANSFER_ORDER_HEADERS
TRANSFER_ORDER_LINE_ID	INV_TRANSFER_ORDER_LINES

Common Tables Used for Joins

Table	Join Condition
RCV_SHIPMENT_HEADERS	RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID = RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
RCV_TRANSACTIONS	RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID = RCV_TRANSACTIONS.SHIPMENT_LINE_ID
PO_HEADERS_ALL	RCV_SHIPMENT_LINES.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
PO_LINES_ALL	RCV_SHIPMENT_LINES.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
PO_LINE_LOCATIONS_ALL	RCV_SHIPMENT_LINES.PO_LINE_LOCATION_ID = PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
PO_DISTRIBUTIONS_ALL	RCV_SHIPMENT_LINES.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
INV_MATERIAL_TXNS	RCV_SHIPMENT_LINES.MMT_TRANSACTION_ID = INV_MATERIAL_TXNS.TRANSACTION_ID
AP_INVOICE_LINES_ALL	RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID = AP_INVOICE_LINES_ALL.RCV_SHIPMENT_LINE_ID

Important Flow


PO_HEADERS_ALL
        ↓
PO_LINES_ALL
        ↓
PO_LINE_LOCATIONS_ALL
        ↓
PO_DISTRIBUTIONS_ALL
        ↓
RCV_SHIPMENT_HEADERS
        ↓
RCV_SHIPMENT_LINES
        ↓
RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS
        ↓
AP_INVOICE_LINES_ALL

Example Query

SELECT
    shipment_line_id,
    line_num,
    item_description,
    quantity_received,
    shipment_line_status_code
FROM rcv_shipment_lines
WHERE shipment_line_status_code = 'FULLY RECEIVED';

Costing

CST_TRANSACTIONS (Costing Transactions Table)

CST_TRANSACTIONS is the main costing transaction table in Oracle Fusion Cost Management. It stores inventory valuation transactions, receipt costing, issue costing, transfer costing, work order costing, COGS recognition, intercompany costing, consigned inventory costing, periodic average costing, and perpetual average costing transactions.

Core Identifiers

Column	Description
TRANSACTION_ID	Primary Key for Costing Transaction
COST_ORG_ID	Cost Organization Identifier
COST_BOOK_ID	Cost Book Identifier
INVENTORY_ITEM_ID	Inventory Item Identifier
INVENTORY_ORG_ID	Inventory Organization Identifier
COST_TRANSACTION_TYPE	Cost Transaction Type
TRANSACTION_DATE	Transaction Date
COST_DATE	Cost Processing Date
PERIOD_NAME	Cost Accounting Period

Valuation Information

Column	Description
VAL_STRUCTURE_ID	Valuation Structure Identifier
VAL_UNIT_ID	Valuation Unit Identifier
VAL_UNIT_COMBINATION_ID	Valuation Unit Combination
VAL_UNIT_DETAIL_ID	Valuation Unit Detail Identifier
STRUCTURE_INSTANCE_NUMBER	Valuation Structure Instance
COST_METHOD_CODE	Cost Method
QUANTITY_FLOW_CODE	FIFO / LIFO Quantity Flow
NEGATIVE_QTY_CODE	Negative Quantity Handling Method
RECEIPT_WITHOUT_COST_CODE	Receipt Without Cost Processing Method
REFERENCED_RMA_COST_CODE	Referenced RMA Cost Method
UNREFERENCED_RMA_COST_CODE	Unreferenced RMA Cost Method

Transaction Type Information

Column	Description
BASE_TXN_TYPE_ID	Inventory Transaction Type
BASE_TXN_SOURCE_TYPE_ID	Base Transaction Source Type
INV_TXN_SOURCE_TYPE_ID	Inventory Transaction Source Type
BASE_TXN_ACTION_ID	Inventory Transaction Action
TXN_SOURCE_DOC_TYPE	Source Document Type
TXN_SOURCE_DOC_NUMBER	Source Document Number
TXN_SOURCE_REF_DOC_TYPE	Reference Document Type
TXN_SOURCE_REF_DOC_NUMBER	Reference Document Number
ADDITIONAL_PROCESSING_CODE	Additional Cost Processing Code

Quantity & UOM Information

Column	Description
QUANTITY	Transaction Quantity
TRANSACTION_QTY	Inventory Transaction Quantity
COSTED_QTY	Processed Costed Quantity
QUANTITY_DEPLETED	Depleted Quantity
UOM_CODE	Costing UOM
TRANSACTION_UOM_CODE	Transaction UOM
COSTING_UOM_CODE	Costing UOM Code
UOM_CONVERSION_FACTOR	UOM Conversion Factor
TRANSFER_UOM_CONVERSION_FACTOR	Transfer UOM Conversion Factor

Costing Status Information

Column	Description
PREPROCESSING_STATUS	Cost Preprocessing Status
COST_STATUS	Current Costing Status
COSTING_STATUS	Detailed Costing Status
ACCOUNTING_STATUS	Accounting Status
POSTED_FLAG	Layer Posting Status
MATCHED_TO_COST_FLAG	Matched to Cost Indicator
MATCHED_TO_ADDL_TAX_FLAG	Additional Tax Match Status
COGS_POSTED_FLAG	COGS Posting Status
PAC_PROCESSED_FLAG	Periodic Average Cost Processing Status
PROCESS_DATE	Cost Processing Date
ERROR_CODE	Costing Error Code

Inventory & Transfer Information

Column	Description
LOGICAL_FLAG	Logical Transaction Indicator
INTRANSIT_FLAG	Intransit Transaction Indicator
TRANSFER_COST_ORG_ID	Transfer Cost Organization
TRANSFER_BOOK_ID	Transfer Cost Book
TRANSFER_VAL_UNIT_ID	Transfer Valuation Unit
TRANSFER_PERCENTAGE	Transfer Cost Percentage
TRANSFER_TRANSACTION_GROUP_ID	Transfer Transaction Group
TRANSFER_CST_INV_TXN_ID	Transfer Inventory Transaction
TRANSFER_CST_INV_TXN_DTL_ID	Transfer Inventory Transaction Detail
FOB_POINT	Free On Board Point
INTERNAL_PROFIT_TRACKING	Internal Profit Tracking Indicator
INTERCOMPANY_INVOICING_FLAG	Intercompany Invoice Indicator

Receiving & Purchasing Information

Column	Description
RCV_TRANSACTION_ID	Receiving Transaction Identifier
PO_DISTRIBUTION_ID	Purchase Order Distribution Identifier
PO_ITEM_DESCRIPTION	Purchase Item Description
PO_ITEM_DESCRIPTION_TYPE	Goods or Services
TRANSACTION_AMOUNT	Purchase Transaction Amount
TRANSACTION_CURRENCY_CODE	Purchase Currency Code
USE_ITEM_COST_FLAG	Use Existing Item Cost Indicator

DOO & Shipping Information

Column	Description
WSH_DELIVERY_DETAIL_ID	Shipping Delivery Detail Identifier
DOO_FULLFILL_LINE_ID	DOO Fulfillment Line Identifier
DOO_SPLIT_FULFILL_LINE_ID	DOO Split Fulfillment Line Identifier
REFERENCE_DOO_FULLFILL_LINE_ID	Referenced DOO Fulfillment Line
REFERENCE_DELIVERY_ID	Reference Delivery Identifier
REFERENCED_RETURN_FLAG	Referenced Return Indicator
SIMPLE_PROC_FLAG	Simple Procurement Indicator

Work Order & Manufacturing Information

Column	Description
CST_WORK_ORDER_ID	Work Order Identifier
CST_WORK_ORDER_OPERATION_ID	Work Order Operation Identifier
WORK_CENTER_ID	Work Center Identifier
CST_WO_OPERATION_TXN_ID	Work Order Operation Transaction
WO_UPDATE_EVENT_TXN_ID	Work Order Close Transaction
SUPPLY_TYPE	WIP Supply Type
SERVICE_WIP_FLAG	Service WIP Indicator
SERVICE_COST_POSTED_FLAG	Service Cost Posting Status
DEBRIEF_LINE_ID	Service Logistics Debrief Line
PARTIAL_COMPLETION_FLAG	Partial Completion Indicator
PROVISIONAL_COMPLETION_TYPE	Work Order Completion Valuation Method
WO_COST_ALLOCATION_BASIS	Work Order Cost Allocation Basis
OPERATION_SCRAP_COST_TYPE	Operation Scrap Cost Treatment

Transfer Order & Consignment Information

Column	Description
CST_TRANSFER_ORDER_LINE_ID	Transfer Order Line Identifier
CST_TRANSFER_ORDER_DIST_ID	Transfer Order Distribution Identifier
TRANSACTION_FLOW_TYPE	Transaction Flow Type
CONSIGNED_FLAG	Consigned Inventory Indicator
CONSIGNED_ACCT_DIST_BASIS	Consigned Accounting Basis
CREATE_ACCT_FOR_CONS_TXNS	Create Accounting for Consigned Transactions
MANUAL_RECEIPT_REQD_FLAG	Manual Receipt Required Indicator

Project & Asset Information

Column	Description
PROJECT_FLAG	Project Enabled Indicator
ASSEMBLY_ASSET_ID	Assembly Asset Identifier

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name
LE_TIMEZONE_CODE	Legal Entity Timezone

Important Notes

Main Costing transaction table in Oracle Fusion Cost Management
Stores Inventory, Receiving, Transfer, WIP, and COGS costing transactions
Used heavily in Cost Accounting and Inventory Valuation
Supports FIFO, LIFO, Standard, PAC, and Perpetual Average costing
Central table for cost layer generation and accounting
Links SCM execution transactions with costing and accounting

Primary Foreign Keys

Column	References Table
CST_INV_TRANSACTION_ID	CST_INV_TRANSACTIONS
COST_BOOK_ID	CST_COST_BOOKS_B
TRANSFER_BOOK_ID	CST_COST_BOOKS_B
CST_INV_TRANSACTION_DTL_ID	CST_INV_TRANSACTION_DTLS
COST_ORG_ID, INVENTORY_ORG_ID	CST_COST_INV_ORGS
VAL_STRUCTURE_ID	CST_VAL_STRUCTURES_B
VAL_UNIT_ID	CST_VAL_UNITS_B
BASE_TXN_TYPE_ID, BASE_TXN_SOURCE_TYPE_ID	CST_INTERNAL_TXN_TYPES_B
COST_PROFILE_ID	CST_COST_PROFILES_B
ITEM_COST_PROFILE_ID	CST_ITEM_COST_PROFILES
TRANSACTION_ID	CST_TRANSACTION_COSTS
TRANSACTION_ID	CST_TRANSACTION_LAYERS

Common Tables Used for Joins

Table	Join Condition
CST_TRANSACTION_COSTS	CST_TRANSACTIONS.TRANSACTION_ID = CST_TRANSACTION_COSTS.TRANSACTION_ID
CST_TRANSACTION_LAYERS	CST_TRANSACTIONS.TRANSACTION_ID = CST_TRANSACTION_LAYERS.TRANSACTION_ID
RCV_TRANSACTIONS	CST_TRANSACTIONS.RCV_TRANSACTION_ID = RCV_TRANSACTIONS.TRANSACTION_ID
PO_DISTRIBUTIONS_ALL	CST_TRANSACTIONS.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
INV_MATERIAL_TXNS	CST_TRANSACTIONS.CST_INV_TRANSACTION_ID = INV_MATERIAL_TXNS.TRANSACTION_ID
DOO_FULFILL_LINES_ALL	CST_TRANSACTIONS.DOO_FULLFILL_LINE_ID = DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID
WSH_DELIVERY_DETAILS	CST_TRANSACTIONS.WSH_DELIVERY_DETAIL_ID = WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID

Important Flow


PO_HEADERS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS
        ↓
CST_TRANSACTIONS
        ↓
CST_TRANSACTION_COSTS
        ↓
CST_TRANSACTION_LAYERS
        ↓
SLA ACCOUNTING

Example Query

SELECT
    transaction_id,
    inventory_item_id,
    quantity,
    cost_method_code,
    cost_status,
    accounting_status
FROM cst_transactions
WHERE cost_status = 'C';

CST_COST_DISTRIBUTIONS (Cost Distribution Headers Table)

CST_COST_DISTRIBUTIONS stores accounting distribution header information for costing transactions in Oracle Fusion Cost Management. It contains SLA event references, cost accounting distribution headers, receipt and issue accounting references, ledger information, cost organization accounting data, gross margin processing status, and project accounting integration details.

Core Identifiers

Column	Description
DISTRIBUTION_ID	Primary Key for Cost Distribution
EVENT_ID	SLA Event Identifier
ENTITY_CODE	SLA Entity Code
EVENT_CLASS_CODE	SLA Event Class
EVENT_TYPE_CODE	SLA Event Type
TRANSACTION_ID	Costing Transaction Identifier
TRANSACTION_NUMBER	Transaction Number

Cost Organization & Ledger Information

Column	Description
LEGAL_ENTITY_ID	Legal Entity Identifier
LEDGER_ID	General Ledger Identifier
COST_ORGANIZATION_ID	Cost Organization Identifier
COST_BOOK_ID	Cost Book Identifier
BASE_CURRENCY_CODE	Legal Entity Base Currency

Transaction Information

Column	Description
REC_TRXN_ID	Receipt Transaction Identifier
DEP_TRXN_ID	Depletion Transaction Identifier
COST_TRANSACTION_TYPE	Issue / Receipt / Adjustment
ADDITIONAL_PROCESSING_CODE	Additional Cost Processing Type
EFF_DATE	Effective Cost Date
EFF_DATE_CHAR	Effective Date Character Value
GL_DATE	Accounting Date
PERIOD_NAME	Costing Period Name
ACCOUNTING_PERIOD_NAME	Accounting Period Name

Quantity & UOM Information

Column	Description
LAYER_QUANTITY	Layer Quantity
COST_TRANSACTION_UOM	Transaction Unit of Measure

Accounting & Processing Status

Column	Description
ACCOUNTED_FLAG	SLA Accounting Status
VAL_ONHAND_FLAG	Onhand Calculation Status
GROSS_MARGIN_FLAG	Gross Margin Processing Status
PJC_TXN_STATUS_CODE	Projects Interface Status

ESS & Audit Information

Column	Description
REQUEST_ID	ESS Request Identifier
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Cost Accounting Distribution Header table in Oracle Fusion Cost Management
Stores SLA accounting distribution references for costing transactions
Used for Inventory Valuation and Cost Accounting
Supports Receipt, Issue, Adjustment, and Transfer accounting
Links Costing transactions with SLA and General Ledger
One Distribution Header can contain multiple Distribution Lines

Primary Foreign Keys

Column	References Table
COST_ORGANIZATION_ID, COST_BOOK_ID	CST_COST_ORG_BOOKS
LEDGER_ID	GL_LEDGERS
TRANSACTION_ID	CST_TRANSACTIONS
DISTRIBUTION_ID	CST_COST_DISTRIBUTION_LINES

Common Tables Used for Joins

Table	Join Condition
CST_TRANSACTIONS	CST_COST_DISTRIBUTIONS.TRANSACTION_ID = CST_TRANSACTIONS.TRANSACTION_ID
CST_COST_DISTRIBUTION_LINES	CST_COST_DISTRIBUTIONS.DISTRIBUTION_ID = CST_COST_DISTRIBUTION_LINES.DISTRIBUTION_ID
GL_LEDGERS	CST_COST_DISTRIBUTIONS.LEDGER_ID = GL_LEDGERS.LEDGER_ID
XLA_EVENTS	CST_COST_DISTRIBUTIONS.EVENT_ID = XLA_EVENTS.EVENT_ID
XLA_AE_HEADERS	CST_COST_DISTRIBUTIONS.EVENT_ID = XLA_AE_HEADERS.EVENT_ID
RCV_TRANSACTIONS	CST_COST_DISTRIBUTIONS.REC_TRXN_ID = RCV_TRANSACTIONS.TRANSACTION_ID
INV_MATERIAL_TXNS	CST_COST_DISTRIBUTIONS.DEP_TRXN_ID = INV_MATERIAL_TXNS.TRANSACTION_ID

Important Flow


RCV_TRANSACTIONS
        ↓
INV_MATERIAL_TXNS
        ↓
CST_TRANSACTIONS
        ↓
CST_COST_DISTRIBUTIONS
        ↓
CST_COST_DISTRIBUTION_LINES
        ↓
XLA_EVENTS
        ↓
GL_JE_HEADERS

Example Query

SELECT
    distribution_id,
    transaction_id,
    event_type_code,
    gl_date,
    accounted_flag
FROM cst_cost_distributions
WHERE accounted_flag = 'F';

CST_COST_BOOKS_B (Cost Books Table)

CST_COST_BOOKS_B stores Cost Book definitions in Oracle Fusion Cost Management. It defines costing books used for inventory valuation, cost accounting, transfer pricing, periodic average costing, perpetual costing, and parallel cost accounting structures. Cost Books are one of the core setup entities in Oracle Fusion Cost Management.

Core Identifiers

Column	Description
COST_BOOK_ID	Primary Key for Cost Book
COST_BOOK_CODE	Unique Cost Book Code
PERIODIC_AVERAGE_FLAG	Periodic Average Cost Book Indicator
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Cost Book Information

Column	Description
COST_BOOK_CODE	Cost Book Name / Purpose Code
PERIODIC_AVERAGE_FLAG	Indicates Periodic Average Costing Book

ESS & Audit Information

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Cost Book setup table in Oracle Fusion Cost Management
Defines Cost Accounting books used for valuation and accounting
Supports Standard, Average, FIFO, LIFO, and Periodic Average costing
Used heavily in Inventory Valuation and Cost Accounting
One Cost Organization can use multiple Cost Books
Central setup table for Cost Management configuration

Primary Foreign Keys

Referenced By Table	Foreign Key Column
CST_COST_BOOKS_TL	COST_BOOK_ID
CST_CP_DRIVERS	COST_BOOK_ID
CST_TRANSACTIONS	COST_BOOK_ID
CST_TRANSACTIONS	TRANSFER_BOOK_ID
CST_USER_ADJUSTMENTS	COST_BOOK_ID
CST_INV_ONHAND_MATCH_T	COST_BOOK_ID
CST_INVOICE_TO_EXP_ACCTG	COST_BOOK_ID
CST_ACQ_ADJ_TXN_COSTS	COST_BOOK_ID
CST_PERIOD_VALIDATIONS	COST_BOOK_ID
CST_COST_ORG_BOOKS	COST_BOOK_ID
CST_ITEM_COST_PROFILES	COST_BOOK_ID
CST_CP_PARAM_DTLS	COST_BOOK_ID

Common Tables Used for Joins

Table	Join Condition
CST_COST_BOOKS_TL	CST_COST_BOOKS_B.COST_BOOK_ID = CST_COST_BOOKS_TL.COST_BOOK_ID
CST_COST_ORG_BOOKS	CST_COST_BOOKS_B.COST_BOOK_ID = CST_COST_ORG_BOOKS.COST_BOOK_ID
CST_TRANSACTIONS	CST_COST_BOOKS_B.COST_BOOK_ID = CST_TRANSACTIONS.COST_BOOK_ID
CST_TRANSACTION_COSTS	CST_COST_BOOKS_B.COST_BOOK_ID = CST_TRANSACTION_COSTS.COST_BOOK_ID
CST_COST_DISTRIBUTIONS	CST_COST_BOOKS_B.COST_BOOK_ID = CST_COST_DISTRIBUTIONS.COST_BOOK_ID
CST_ITEM_COST_PROFILES	CST_COST_BOOKS_B.COST_BOOK_ID = CST_ITEM_COST_PROFILES.COST_BOOK_ID
CST_PERIOD_VALIDATIONS	CST_COST_BOOKS_B.COST_BOOK_ID = CST_PERIOD_VALIDATIONS.COST_BOOK_ID

Important Flow


CST_COST_BOOKS_B
        ↓
CST_COST_ORG_BOOKS
        ↓
CST_TRANSACTIONS
        ↓
CST_TRANSACTION_COSTS
        ↓
CST_COST_DISTRIBUTIONS
        ↓
SLA ACCOUNTING

Example Query

SELECT
    cost_book_id,
    cost_book_code,
    periodic_average_flag
FROM cst_cost_books_b
ORDER BY cost_book_code;

Product Information Management (PIM)

EGP_ITEM_REVISIONS_B (Item Revisions Table)

EGP_ITEM_REVISIONS_B stores item revision information in Oracle Fusion Product Information Management (PIM). It contains revision history, engineering change references, revision effectivity dates, lifecycle phase tracking, and revision control information for inventory items. This table is heavily used in Inventory, Manufacturing, Product Development, Engineering Change Orders, and Supply Chain execution.

Core Identifiers

Column	Description
REVISION_ID	Primary Key for Item Revision
INVENTORY_ITEM_ID	Inventory Item Identifier
ORGANIZATION_ID	Inventory Organization Identifier
REVISION	Item Revision Code
ALT_REVISION_CODE	Alternate Revision Code
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Revision Information

Column	Description
EFFECTIVITY_DATE	Revision Effective Start Date
END_EFFECTIVITY_DATE	Revision Effective End Date
IMPLEMENTATION_DATE	Revision Implementation Date
ECN_INITIATION_DATE	Engineering Change Initiation Date
REVISION_REASON	Reason for Revision Change
DESCRIPTION	Revision Description

Engineering Change Information

Column	Description
CHANGE_LINE_ID	Engineering Change Order Line
CURRENT_PHASE_ID	Lifecycle Phase Identifier

Program & ESS Information

Column	Description
REQUEST_ID	ESS Request Identifier
PROGRAM_NAME	Concurrent Program Name
PROGRAM_APP_NAME	Concurrent Program Application
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Item Revision table in Oracle Fusion Product Management
Stores revision-controlled inventory item revisions
Used heavily in Engineering Change Management and Manufacturing
Tracks effective dates and lifecycle phases of revisions
Supports ECO and Product Lifecycle Management processes
One Item can contain multiple revisions over time

Primary Foreign Keys

Column	References Table
INVENTORY_ITEM_ID, ORGANIZATION_ID	EGP_SYSTEM_ITEMS_B
REVISION_ID	EGO_ITEM_REVISION_EFF_TL
REVISION_ID	EGP_ITEM_REVISIONS_INTERFACE
REVISION_ID	EGP_ITEM_REVISIONS_TL
REVISION_ID	EGO_ITEM_REVISION_EFF_B

Common Tables Used for Joins

Table	Join Condition
EGP_SYSTEM_ITEMS_B	EGP_ITEM_REVISIONS_B.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID AND EGP_ITEM_REVISIONS_B.ORGANIZATION_ID = EGP_SYSTEM_ITEMS_B.ORGANIZATION_ID
EGP_SYSTEM_ITEMS_TL	EGP_ITEM_REVISIONS_B.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_TL.INVENTORY_ITEM_ID AND EGP_ITEM_REVISIONS_B.ORGANIZATION_ID = EGP_SYSTEM_ITEMS_TL.ORGANIZATION_ID
EGP_ITEM_REVISIONS_TL	EGP_ITEM_REVISIONS_B.REVISION_ID = EGP_ITEM_REVISIONS_TL.REVISION_ID
EGO_ITEM_REVISION_EFF_B	EGP_ITEM_REVISIONS_B.REVISION_ID = EGO_ITEM_REVISION_EFF_B.REVISION_ID
INV_MATERIAL_TXNS	EGP_ITEM_REVISIONS_B.REVISION = INV_MATERIAL_TXNS.REVISION
RCV_TRANSACTIONS	EGP_ITEM_REVISIONS_B.REVISION = RCV_TRANSACTIONS.ITEM_REVISION

Important Flow


EGP_SYSTEM_ITEMS_B
        ↓
EGP_ITEM_REVISIONS_B
        ↓
EGO_ITEM_REVISION_EFF_B
        ↓
INV_MATERIAL_TXNS
        ↓
RCV_TRANSACTIONS
        ↓
WORK ORDERS / MANUFACTURING

Example Query

SELECT
    revision_id,
    inventory_item_id,
    revision,
    effectivity_date,
    implementation_date
FROM egp_item_revisions_b
ORDER BY inventory_item_id, effectivity_date;

EGP_CATEGORIES_B (Item Categories Table)

EGP_CATEGORIES_B stores item category definitions in Oracle Fusion Product Information Management (PIM). It contains category hierarchy information, UNSPSC classifications, supplier categories, procurement categories, inventory categories, and category setup used across SCM, Procurement, Inventory, Costing, Receiving, and Product Management.

Core Identifiers

Column	Description
CATEGORY_ID	Primary Key for Category
CATEGORY_CODE	Unique Category Code
DESCRIPTION	Category Description
STRUCTURE_ID	Category Flexfield Structure
STRUCTURE_INSTANCE_NUMBER	Category Structure Instance
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Category Segment Information

Column	Description
SEGMENT1	Category Segment 1
SEGMENT2	Category Segment 2
SEGMENT3	Category Segment 3
SEGMENT4	Category Segment 4
SEGMENT5	Category Segment 5
SEGMENT6	Category Segment 6
SEGMENT7	Category Segment 7
SEGMENT8	Category Segment 8
SEGMENT9	Category Segment 9
SEGMENT10	Category Segment 10

Category Status Information

Column	Description
START_DATE_ACTIVE	Category Active Start Date
END_DATE_ACTIVE	Category Active End Date
WEB_STATUS	Web Publication Status
SUPPLIER_ENABLED_FLAG	Supplier Profile Enabled Indicator
CATEGORY_CONTENT_CODE	Category Content Type

Program & ESS Information

Column	Description
REQUEST_ID	ESS Request Identifier
PROGRAM_NAME	Concurrent Program Name
PROGRAM_APP_NAME	Concurrent Program Application
JOB_DEFINITION_NAME	ESS Job Definition Name
JOB_DEFINITION_PACKAGE	ESS Job Package Name

Audit Columns

Column	Description
CREATION_DATE	Record Creation Date
CREATED_BY	Created By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Category Master table in Oracle Fusion PIM
Stores Procurement, Inventory, and Product categories
Used heavily across SCM, Procurement, Inventory, and Costing
Supports category hierarchies and category sets
Used for UNSPSC and item classification structures
One Category can belong to multiple Category Sets

Primary Foreign Keys

Referenced By Table	Foreign Key Column
INV_SELECTION_CRITERIA_TXN	CATEGORY_ID
EGP_CATG_MAP_DTLS	SOURCE_CATG_ID
EGP_CATG_MAP_DTLS	TARGET_CATG_ID
AR_REVENUE_ADJUSTMENTS_ALL	FROM_CATEGORY_ID
AR_REVENUE_ADJUSTMENTS_ALL	TO_CATEGORY_ID
INV_SUPPLY	CATEGORY_ID
PO_LINE_TYPES_B	CATEGORY_ID
PO_AGENTS	CATEGORY_ID
RCV_SHIPMENT_LINES	CATEGORY_ID
EGP_CATEGORY_SET_VALID_CATS	CATEGORY_ID
PO_LINES_ALL	CATEGORY_ID
PO_LINES_DRAFT_ALL	CATEGORY_ID
PON_AUCTION_ITEM_PRICES_ALL	CATEGORY_ID
EGP_CATEGORIES_TL	CATEGORY_ID
POR_SMART_FORMS_ALL_B	CATEGORY_ID
CST_DEFAULT_COST_PROFILES	CATEGORY_ID

Common Tables Used for Joins

Table	Join Condition
EGP_ITEM_CATEGORIES	EGP_CATEGORIES_B.CATEGORY_ID = EGP_ITEM_CATEGORIES.CATEGORY_ID
EGP_CATEGORY_SETS_B	EGP_ITEM_CATEGORIES.CATEGORY_SET_ID = EGP_CATEGORY_SETS_B.CATEGORY_SET_ID
PO_LINES_ALL	EGP_CATEGORIES_B.CATEGORY_ID = PO_LINES_ALL.CATEGORY_ID
RCV_SHIPMENT_LINES	EGP_CATEGORIES_B.CATEGORY_ID = RCV_SHIPMENT_LINES.CATEGORY_ID
EGP_CATEGORIES_TL	EGP_CATEGORIES_B.CATEGORY_ID = EGP_CATEGORIES_TL.CATEGORY_ID
INV_SUPPLY	EGP_CATEGORIES_B.CATEGORY_ID = INV_SUPPLY.CATEGORY_ID
CST_DEFAULT_COST_PROFILES	EGP_CATEGORIES_B.CATEGORY_ID = CST_DEFAULT_COST_PROFILES.CATEGORY_ID
POR_SMART_FORMS_ALL_B	EGP_CATEGORIES_B.CATEGORY_ID = POR_SMART_FORMS_ALL_B.CATEGORY_ID

Important Flow


EGP_CATEGORIES_B
        ↓
EGP_CATEGORY_SETS_B
        ↓
EGP_ITEM_CATEGORIES
        ↓
EGP_SYSTEM_ITEMS_B
        ↓
PO_LINES_ALL
        ↓
RCV_SHIPMENT_LINES
        ↓
CST_TRANSACTIONS

Example Query

SELECT
    category_id,
    category_code,
    description,
    segment1,
    start_date_active
FROM egp_categories_b
ORDER BY category_code;

Common / Reference Tables

FUN_ALL_BUSINESS_UNITS_V (Business Units Reference View)

FUN_ALL_BUSINESS_UNITS_V stores Business Unit reference information in Oracle Fusion Applications. It provides Business Unit setup details, ledger mapping, legal entity relationships, currency defaults, HR enablement flags, profit center configuration, and organizational hierarchy information. This is one of the most commonly used reference views across Finance, Procurement, SCM, HCM, Projects, and Order Management.

Core Identifiers

Column	Description
BU_ID	Business Unit Identifier
BU_NAME	Business Unit Name
SHORT_CODE	Business Unit Short Code
STATUS	Business Unit Status

Organization Information

Column	Description
LOCATION_ID	Business Unit Location Identifier
BUSINESS_GROUP_ID	Business Group Identifier
DATE_FROM	Business Unit Effective Start Date
DATE_TO	Business Unit Effective End Date
MANAGER_ID	Business Unit Manager Identifier
FIN_BUSINESS_UNIT_ID	Financial Business Unit Identifier

Finance & Ledger Information

Column	Description
LEGAL_ENTITY_ID	Legal Entity Identifier
PRIMARY_LEDGER_ID	Primary Ledger Identifier
DEFAULT_SET_ID	Reference Data Set Identifier
DEFAULT_CURRENCY_CODE	Default Currency Code
PROFIT_CENTER_FLAG	Profit Center Indicator

HR & Operational Information

Column	Description
ENABLED_FOR_HR_FLAG	HR Enabled Indicator

Audit Columns

Column	Description
CREATED_BY	Created By User
CREATION_DATE	Record Creation Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATE_LOGIN	Last Update Login Session

Underlying Base Tables

Table	Purpose
HR_ALL_ORGANIZATION_UNITS_F_VL	Organization Master Information
HR_ORG_UNIT_CLASSIFICATIONS_X	Organization Classification Details
HR_ORGANIZATION_INFORMATION_X	Organization Additional Information

Important Notes

Main Business Unit reference view in Oracle Fusion Applications
Used across Procurement, SCM, Finance, HCM, and Projects
Provides mapping between Business Units and Legal Entities
Stores ledger and currency configuration for Business Units
Frequently used in reporting and security filtering
One of the most important common reference views in Fusion

Common Tables Used for Joins

Table	Join Condition
PO_HEADERS_ALL	FUN_ALL_BUSINESS_UNITS_V.BU_ID = PO_HEADERS_ALL.PRC_BU_ID
POR_REQUISITION_HEADERS_ALL	FUN_ALL_BUSINESS_UNITS_V.BU_ID = POR_REQUISITION_HEADERS_ALL.REQ_BU_ID
DOO_HEADERS_ALL	FUN_ALL_BUSINESS_UNITS_V.BU_ID = DOO_HEADERS_ALL.ORG_ID
INV_ORGANIZATION_DEFINITIONS_V	FUN_ALL_BUSINESS_UNITS_V.BU_ID = INV_ORGANIZATION_DEFINITIONS_V.BUSINESS_UNIT_ID
XLE_ENTITY_PROFILES	FUN_ALL_BUSINESS_UNITS_V.LEGAL_ENTITY_ID = XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID
GL_LEDGERS	FUN_ALL_BUSINESS_UNITS_V.PRIMARY_LEDGER_ID = GL_LEDGERS.LEDGER_ID
HZ_CUST_ACCOUNTS	FUN_ALL_BUSINESS_UNITS_V.BU_ID = HZ_CUST_ACCOUNTS.SELLING_PARTY_ID
POZ_SUPPLIERS	FUN_ALL_BUSINESS_UNITS_V.BU_ID = POZ_SUPPLIERS.PRC_BU_ID

Important Flow


FUN_ALL_BUSINESS_UNITS_V
        ↓
LEGAL ENTITY
        ↓
LEDGER
        ↓
PROCUREMENT / SCM / FINANCE
        ↓
PO_HEADERS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
CST_TRANSACTIONS

View Query Logic

SELECT ORG.ORGANIZATION_ID BU_ID,
       ORG.NAME BU_NAME,
       ORG.LOCATION_ID,
       ORG.BUSINESS_GROUP_ID,
       ORG.EFFECTIVE_START_DATE DATE_FROM,
       ORG.EFFECTIVE_END_DATE DATE_TO,
       INFO.ORG_INFORMATION1 MANAGER_ID,
       INFO.ORG_INFORMATION2 LEGAL_ENTITY_ID,
       INFO.ORG_INFORMATION3 PRIMARY_LEDGER_ID
FROM HR_ALL_ORGANIZATION_UNITS_F_VL ORG

Example Query

SELECT
    bu_id,
    bu_name,
    legal_entity_id,
    primary_ledger_id,
    default_currency_code,
    status
FROM fun_all_business_units_v
ORDER BY bu_name;

XLE_ENTITY_PROFILES (Legal Entity Profiles Table)

XLE_ENTITY_PROFILES stores Legal Entity master information in Oracle Fusion Applications. It contains legal entity registration details, party relationships, geography mappings, payroll statutory unit information, legal employer setup, and enterprise legal structure information. This is one of the most important common reference tables used across Finance, SCM, Procurement, HCM, Tax, Payments, and Intercompany modules.

Core Identifiers

Column	Description
LEGAL_ENTITY_ID	Primary Key for Legal Entity
PARTY_ID	HZ Party Identifier
LEGAL_ENTITY_IDENTIFIER	Legal Registration Identifier
NAME	Legal Entity Name
GEOGRAPHY_ID	Geography Identifier
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Legal Entity Information

Column	Description
TRANSACTING_ENTITY_FLAG	Business Transaction Enabled Indicator
EFFECTIVE_FROM	Effective Start Date
EFFECTIVE_TO	Effective End Date
TYPE_OF_COMPANY	Type of Company
ACTIVITY_CODE	Primary Business Activity
SUB_ACTIVITY_CODE	Sub Business Activity
ENTERPRISE_ID	Enterprise Identifier

Payroll & Employer Information

Column	Description
PSU_FLAG	Payroll Statutory Unit Indicator
LEGAL_EMPLOYER_FLAG	Legal Employer Indicator
PARENT_PSU_ID	Parent Payroll Statutory Unit

Flexfield Context Information

Column	Description
LE_INFORMATION_CONTEXT	Legal Entity DFF Context

Audit Columns

Column	Description
CREATED_BY	Created By User
CREATION_DATE	Record Creation Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Legal Entity master table in Oracle Fusion
Used across Finance, SCM, HCM, Procurement, Tax, and Payments
Stores Legal Entity registration and enterprise structure details
Links Legal Entities with Parties and Geographies
Supports Payroll Statutory Unit and Legal Employer configuration
Critical reference table for ledger and business unit setup

Primary Foreign Keys

Column	References Table
PARTY_ID	HZ_PARTIES
GEOGRAPHY_ID	HZ_GEOGRAPHIES

Referenced By Important Tables

Table	Foreign Key Column
FUN_ALL_BUSINESS_UNITS_V	LEGAL_ENTITY_ID
AP_INVOICES_ALL	LEGAL_ENTITY_ID
AR_CASH_RECEIPTS_ALL	LEGAL_ENTITY_ID
RA_CUSTOMER_TRX_ALL	LEGAL_ENTITY_ID
AP_CHECKS_ALL	LEGAL_ENTITY_ID
FUN_INTERCO_ORGANIZATIONS	LEGAL_ENTITY_ID
CE_PAYMENT_INSTRUMENTS	LEGAL_ENTITY_ID
IBY_GEN_DOCS_PAYABLE	LEGAL_ENTITY_ID
OKC_K_HEADERS_ALL_B	LEGAL_ENTITY_ID
ZX_TRX_TELEMETRY_DATA	LEGAL_ENTITY_ID

Common Tables Used for Joins

Table	Join Condition
HZ_PARTIES	XLE_ENTITY_PROFILES.PARTY_ID = HZ_PARTIES.PARTY_ID
HZ_GEOGRAPHIES	XLE_ENTITY_PROFILES.GEOGRAPHY_ID = HZ_GEOGRAPHIES.GEOGRAPHY_ID
FUN_ALL_BUSINESS_UNITS_V	XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID = FUN_ALL_BUSINESS_UNITS_V.LEGAL_ENTITY_ID
GL_LEDGERS	FUN_ALL_BUSINESS_UNITS_V.PRIMARY_LEDGER_ID = GL_LEDGERS.LEDGER_ID
AP_INVOICES_ALL	XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID = AP_INVOICES_ALL.LEGAL_ENTITY_ID
RA_CUSTOMER_TRX_ALL	XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID = RA_CUSTOMER_TRX_ALL.LEGAL_ENTITY_ID
AR_CASH_RECEIPTS_ALL	XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID = AR_CASH_RECEIPTS_ALL.LEGAL_ENTITY_ID
FUN_INTERCO_ORGANIZATIONS	XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID = FUN_INTERCO_ORGANIZATIONS.LEGAL_ENTITY_ID

Important Flow


XLE_ENTITY_PROFILES
        ↓
FUN_ALL_BUSINESS_UNITS_V
        ↓
GL_LEDGERS
        ↓
PROCUREMENT / FINANCE / SCM
        ↓
AP / AR / SCM TRANSACTIONS

Example Query

SELECT
    legal_entity_id,
    name,
    legal_entity_identifier,
    legal_employer_flag,
    psu_flag
FROM xle_entity_profiles
ORDER BY name;

INV_ORG_PARAMETERS (Inventory Organization Parameters Table)

INV_ORG_PARAMETERS stores Inventory Organization setup and operational parameter information in Oracle Fusion Inventory Management. It contains inventory control settings, picking rules, locator controls, lot and serial generation controls, warehouse execution flags, manufacturing flags, costing behavior, and inventory organization configuration details. This is one of the most important Inventory setup tables in Oracle Fusion SCM.

Core Identifiers

Column	Description
ORGANIZATION_ID	Inventory Organization Identifier
ORGANIZATION_CODE	Inventory Organization Code
MASTER_ORGANIZATION_ID	Master Inventory Organization
BUSINESS_UNIT_ID	Business Unit Identifier
LEGAL_ENTITY_ID	Legal Entity Identifier
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Inventory Control Information

Column	Description
SCHEDULE_ID	Cycle Count Schedule Identifier
DEFAULT_PICKING_RULE_ID	Default Picking Rule
DEFAULT_LOCATOR_ORDER_VALUE	Default Locator Control Method
DEFAULT_SUBINV_ORDER_VALUE	Default Subinventory Ordering Method
NEGATIVE_INV_RECEIPT_CODE	Negative Inventory Receipt Control
STOCK_LOCATOR_CONTROL_CODE	Stock Locator Control Method
SERIAL_NUMBER_TYPE	Serial Number Control Type
AUTO_SERIAL_ALPHA_PREFIX	Auto Serial Prefix
START_AUTO_SERIAL_NUMBER	Starting Auto Serial Number
AUTO_LOT_ALPHA_PREFIX	Auto Lot Prefix
LOT_NUMBER_UNIQUENESS	Lot Number Uniqueness Control
LOT_NUMBER_GENERATION	Lot Number Generation Method
LOT_NUMBER_ZERO_PADDING	Lot Number Zero Padding
LOT_NUMBER_LENGTH	Lot Number Length
STARTING_REVISION	Starting Item Revision
SERIAL_NUMBER_GENERATION	Serial Number Generation Method
GLOBAL_ATTRIBUTE_CATEGORY	Global Attribute Category

Warehouse & Picking Information

Column	Description
MO_PICK_CONFIRM_REQUIRED	Move Order Pick Confirm Requirement
PRE_FILL_PICKED_QTY	Pre-fill Picked Quantity Flag
CAPTURE_PICKING_EXCEPTION	Capture Picking Exceptions
WCS_ENABLED	Warehouse Control System Enabled
QA_SKIPPING_INSP_FLAG	Skip QA Inspection Indicator
EAM_ENABLED_FLAG	Enterprise Asset Management Enabled

Manufacturing & Costing Information

Column	Description
PROJECT_REFERENCE_ENABLED	Project Reference Enabled
FIFO_ORG_RCPT_DATE_FLAG	FIFO Receipt Date Control
ALLOW_NEG_ONHAND_CC_TXNS	Allow Negative Onhand Transactions
PURCHASING_BY_REVISION	Purchasing by Revision Control
AUTO_BREAKDOWN_ENABLED	Automatic Breakdown Enabled
PROFIT_CENTER_BU_ID	Profit Center Business Unit
MANUAL_RECEIPT_EXP_AT_DEST	Manual Receipt at Destination
MFG_PLANT_FLAG	Manufacturing Plant Indicator
CONTRACT_MFG_FLAG	Contract Manufacturing Indicator
USE_CURR_ITEM_COST_FLAG	Use Current Item Cost Indicator

Supplier & Integration Information

Column	Description
SUPPLIER_ID	Supplier Identifier
SUPPLIER_SITE_ID	Supplier Site Identifier
TIMEZONE_CODE	Organization Timezone
INTEGRATED_SYSTEM_CODE	Integrated External System
ITEM_GROUPING_CODE	Item Grouping Code
ITEM_DEFINITION_ORG_ID	Item Definition Organization

Operational Flags

Column	Description
INTERNAL_CUSTOMER_FLAG	Internal Customer Enabled
SPECIAL_HANDLING_OVRPK_FLAG	Special Handling Overpack Indicator
FA_BOOK_TYPE_CODE	Fixed Assets Book Type
ALLOW_ITEM_SUBSTITUTIONS	Allow Item Substitutions
WMS_WITH_MFG	Warehouse Management with Manufacturing
ALM_FILL_KILL_SALES_ORDER_FLAG	ALM Fill Kill Sales Order Indicator
KANBAN_CARD_PREFIX	Kanban Card Prefix
MAINT_TECH_WB_ENABLED_FLAG	Maintenance Technician Workbench Enabled
EXPIRED_LOT_SUBINVENTORY	Expired Lot Subinventory
EXPIRED_LOT_LOCATOR	Expired Lot Locator
LOT_CONVERSION_ENABLED_FLAG	Lot Conversion Enabled
AUTOCRT_LOT_UOM_CONV_FLAG	Automatic Lot UOM Conversion

Audit Columns

Column	Description
CREATED_BY	Created By User
CREATION_DATE	Record Creation Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Inventory Organization setup table in Oracle Fusion Inventory
Controls inventory behavior at organization level
Stores lot, serial, locator, picking, and warehouse parameters
Used heavily in Inventory, WMS, Manufacturing, and Costing
Controls organization-wide inventory transaction behavior
Critical setup table for SCM implementations

Primary Foreign Keys

Column	References Table
BUSINESS_UNIT_ID	FUN_ALL_BUSINESS_UNITS_V
LEGAL_ENTITY_ID	XLE_ENTITY_PROFILES
SUPPLIER_ID	POZ_SUPPLIERS
SUPPLIER_SITE_ID	POZ_SUPPLIER_SITES_ALL_M
ITEM_DEFINITION_ORG_ID	INV_ORG_PARAMETERS
MASTER_ORGANIZATION_ID	INV_ORG_PARAMETERS
SOURCE_SUBINVENTORY, SOURCE_ORGANIZATION_ID	INV_SECONDARY_INVENTORIES
ORGANIZATION_ID	INV_SECONDARY_INVENTORIES

Referenced By Important Tables

Table	Foreign Key Column
INV_ONHAND_QUANTITIES_DETAIL	ORGANIZATION_ID
INV_MATERIAL_TXNS	ORGANIZATION_ID
INV_SECONDARY_INVENTORIES	ORGANIZATION_ID
INV_ITEM_LOCATIONS	ORGANIZATION_ID
INV_SERIAL_NUMBERS	CURRENT_ORGANIZATION_ID
INV_LICENSE_PLATE_NUMBERS	ORGANIZATION_ID
INV_LOT_NUMBERS	ORGANIZATION_ID
INV_SUPPLY	TO_ORGANIZATION_ID
INV_RESERVATIONS	ORGANIZATION_ID
INV_TXN_REQUEST_HEADERS	ORGANIZATION_ID
INV_TXN_REQUEST_LINES	ORGANIZATION_ID
INV_TXN_REQUEST_LINES	TO_ORGANIZATION_ID
INV_INTERORG_PARAMETERS	FROM_ORGANIZATION_ID
INV_INTERORG_PARAMETERS	TO_ORGANIZATION_ID
INV_TRANSACTION_FLOW_HEADERS	ORGANIZATION_ID
INV_TRANSACTION_FLOW_LINES	FROM_ORGANIZATION_ID
WSH_SHIPPING_PARAMETERS	ORGANIZATION_ID
WSH_ORG_CARRIER_SERVICES	ORGANIZATION_ID
OKC_K_HEADERS_ALL_B	INV_ORGANIZATION_ID
OKC_K_LINES_B	SHIP_INV_ORG_ID

Common Tables Used for Joins

Table	Join Condition
INV_MATERIAL_TXNS	INV_ORG_PARAMETERS.ORGANIZATION_ID = INV_MATERIAL_TXNS.ORGANIZATION_ID
INV_ONHAND_QUANTITIES_DETAIL	INV_ORG_PARAMETERS.ORGANIZATION_ID = INV_ONHAND_QUANTITIES_DETAIL.ORGANIZATION_ID
EGP_SYSTEM_ITEMS_B	INV_ORG_PARAMETERS.ORGANIZATION_ID = EGP_SYSTEM_ITEMS_B.ORGANIZATION_ID
FUN_ALL_BUSINESS_UNITS_V	INV_ORG_PARAMETERS.BUSINESS_UNIT_ID = FUN_ALL_BUSINESS_UNITS_V.BU_ID
XLE_ENTITY_PROFILES	INV_ORG_PARAMETERS.LEGAL_ENTITY_ID = XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID
RCV_TRANSACTIONS	INV_ORG_PARAMETERS.ORGANIZATION_ID = RCV_TRANSACTIONS.ORGANIZATION_ID
INV_SECONDARY_INVENTORIES	INV_ORG_PARAMETERS.ORGANIZATION_ID = INV_SECONDARY_INVENTORIES.ORGANIZATION_ID
INV_ITEM_LOCATIONS	INV_ORG_PARAMETERS.ORGANIZATION_ID = INV_ITEM_LOCATIONS.ORGANIZATION_ID

Important Flow


FUN_ALL_BUSINESS_UNITS_V
        ↓
XLE_ENTITY_PROFILES
        ↓
INV_ORG_PARAMETERS
        ↓
EGP_SYSTEM_ITEMS_B
        ↓
INV_MATERIAL_TXNS
        ↓
INV_ONHAND_QUANTITIES_DETAIL

Example Query

SELECT
    organization_id,
    organization_code,
    business_unit_id,
    legal_entity_id,
    timezone_code
FROM inv_org_parameters
ORDER BY organization_code;

HZ_LOCATIONS (Location Master Table)

HZ_LOCATIONS stores address and physical location information in Oracle Fusion Trading Community Architecture (TCA). It contains supplier addresses, customer addresses, site addresses, geography information, postal details, tax location references, and physical delivery locations. This is one of the most commonly used reference tables across SCM, Procurement, Order Management, Receivables, Payables, Shipping, and HCM.

Core Identifiers

Column	Description
LOCATION_ID	Primary Key for Location
ORG_SYSTEM_REFERENCE	Source System Reference
COUNTRY	Country Code
STATE	State / Province
CITY	City Name
POSTAL_CODE	Postal Code
COUNTY	County / District
ADDRESS_STYLE	Address Style Format
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Address Information

Column	Description
ADDRESS1	Address Line 1
ADDRESS2	Address Line 2
ADDRESS3	Address Line 3
ADDRESS4	Address Line 4
SALES_TAX_GEOCODE	Sales Tax Geography Code
SALES_TAX_INSIDE_CITY_LIMITS	Inside City Limits Indicator
FA_LOCATION_ID	Fixed Asset Location Identifier

Geography & Validation Information

Column	Description
VALIDATED_FLAG	Address Validation Status
GEOMETRY_STATUS_CODE	Geography Validation Status
ACTUAL_CONTENT_SOURCE	Address Source
CREATED_BY_MODULE	Module That Created Address
ADDRESS_CONFLICT_ID	Address Conflict Identifier
ADDRESS_STATUS_FLAG	Address Status
INTERNAL_FLAG	Internal Address Indicator

Audit Columns

Column	Description
CREATED_BY	Created By User
CREATION_DATE	Record Creation Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Address Master table in Oracle Fusion TCA
Stores Supplier, Customer, Employee, and Internal addresses
Used heavily in Procurement, SCM, OM, AP, AR, and HCM
Central address repository for Trading Community Architecture
Supports geography validation and tax calculations
One Location can be linked to multiple Party Sites

Primary Foreign Keys

Referenced By Table	Foreign Key Column
SVC_WORK_ORDERS	ADDRESS_ID
HER_LOCATION_ASSOCIATION	LOCATION_ID
HER_ORG_LOCATION_EXTENSION	LOCATION_ID
IBY_CREDITCARD	UPGRADE_ADDRESSID
ZX_LINES_DET_FACTORS	SHIP_TO_LOCATION_ID
ZX_LINES_DET_FACTORS	SHIP_FROM_LOCATION_ID
ZX_LINES_DET_FACTORS	BILL_TO_LOCATION_ID
ZX_LINES_DET_FACTORS	BILL_FROM_LOCATION_ID
AR_REMIT_TO_LOCS_ALL	LOCATION_ID
HZ_LOCATION_PROFILES	LOCATION_ID
HER_BUILDING_B	LOCATION_ID
HER_CURRIC_SCHEDULE_B	LOCATION_ID
HZ_PARTY_SITES	LOCATION_ID
HZ_LOCATION_LOCALES	LOCATION_ID

Common Tables Used for Joins

Table	Join Condition
HZ_PARTY_SITES	HZ_LOCATIONS.LOCATION_ID = HZ_PARTY_SITES.LOCATION_ID
HZ_PARTIES	HZ_PARTY_SITES.PARTY_ID = HZ_PARTIES.PARTY_ID
POZ_SUPPLIER_SITES_ALL_M	HZ_LOCATIONS.LOCATION_ID = POZ_SUPPLIER_SITES_ALL_M.LOCATION_ID
HZ_CUST_ACCT_SITES_ALL	HZ_LOCATIONS.LOCATION_ID = HZ_CUST_ACCT_SITES_ALL.LOCATION_ID
FUN_ALL_BUSINESS_UNITS_V	HZ_LOCATIONS.LOCATION_ID = FUN_ALL_BUSINESS_UNITS_V.LOCATION_ID
ZX_LINES_DET_FACTORS	HZ_LOCATIONS.LOCATION_ID = ZX_LINES_DET_FACTORS.SHIP_TO_LOCATION_ID
AR_REMIT_TO_LOCS_ALL	HZ_LOCATIONS.LOCATION_ID = AR_REMIT_TO_LOCS_ALL.LOCATION_ID
HER_LOCATION_ASSOCIATION	HZ_LOCATIONS.LOCATION_ID = HER_LOCATION_ASSOCIATION.LOCATION_ID

Important Flow


HZ_LOCATIONS
        ↓
HZ_PARTY_SITES
        ↓
SUPPLIER / CUSTOMER SITES
        ↓
POZ_SUPPLIER_SITES_ALL_M
        ↓
PO_HEADERS_ALL / DOO_HEADERS_ALL
        ↓
RCV_TRANSACTIONS

Example Query

SELECT
    location_id,
    country,
    city,
    address1,
    postal_code,
    validated_flag
FROM hz_locations
ORDER BY location_id;

HR_ALL_ORGANIZATION_UNITS_F (Organizations Master Table)

HR_ALL_ORGANIZATION_UNITS_F stores organization structure information in Oracle Fusion HCM. It contains Business Units, Departments, Inventory Organizations, Legal Employers, Divisions, and Enterprise organizational hierarchy details. This is one of the core organization master tables used across HCM, SCM, Finance, Procurement, Payroll, and Projects.

Core Identifiers

Column	Description
ORGANIZATION_ID	Primary Key for Organization
MODULE_ID	Owning Application Module Identifier
BUSINESS_GROUP_ID	Business Group Identifier
LEGAL_ENTITY_ID	Legal Entity Identifier
ESTABLISHMENT_ID	Establishment Identifier
INTERNAL_EXTERNAL_FLAG	Internal / External Organization Indicator
OBJECT_VERSION_NUMBER	Optimistic Lock Version

Organization Effective Dates

Column	Description
EFFECTIVE_START_DATE	Organization Effective Start Date
EFFECTIVE_END_DATE	Organization Effective End Date
ACTION_OCCURRENCE_ID	Action Occurrence Identifier

Organization Information

Column	Description
ORGANIZATION_CODE	Organization Code
SEED_DATA_SOURCE	Seed Data Source Information

Audit Columns

Column	Description
CREATED_BY	Created By User
CREATION_DATE	Record Creation Date
LAST_UPDATED_BY	Last Updated By User
LAST_UPDATE_DATE	Last Updated Date
LAST_UPDATE_LOGIN	Last Update Login Session

Important Notes

Main Organization master table in Oracle Fusion HCM
Stores Business Units, Departments, Legal Employers, and Organizations
Used heavily across HCM, SCM, Finance, and Procurement
Date-effective table storing organization history over time
Forms the base for organizational hierarchy structures
One of the most important enterprise setup tables in Fusion

Primary Foreign Keys

Referenced By Table	Foreign Key Column
IRC_MP_GIGS_B	CLASSIFY_ORG_ID

Common Tables Used for Joins

Table	Join Condition
FUN_ALL_BUSINESS_UNITS_V	HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID = FUN_ALL_BUSINESS_UNITS_V.BU_ID
INV_ORG_PARAMETERS	HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID = INV_ORG_PARAMETERS.ORGANIZATION_ID
XLE_ENTITY_PROFILES	HR_ALL_ORGANIZATION_UNITS_F.LEGAL_ENTITY_ID = XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID
HR_ORGANIZATION_INFORMATION_X	HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID = HR_ORGANIZATION_INFORMATION_X.ORGANIZATION_ID
HR_ORG_UNIT_CLASSIFICATIONS_X	HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID = HR_ORG_UNIT_CLASSIFICATIONS_X.ORGANIZATION_ID
PER_ALL_ASSIGNMENTS_M	HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID = PER_ALL_ASSIGNMENTS_M.ORGANIZATION_ID
HR_LOCATIONS_ALL	HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID = HR_LOCATIONS_ALL.ORGANIZATION_ID
IRC_MP_GIGS_B	HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID = IRC_MP_GIGS_B.CLASSIFY_ORG_ID

Important Flow


HR_ALL_ORGANIZATION_UNITS_F
        ↓
FUN_ALL_BUSINESS_UNITS_V
        ↓
INV_ORG_PARAMETERS
        ↓
PO_HEADERS_ALL
        ↓
RCV_TRANSACTIONS
        ↓
CST_TRANSACTIONS

Example Query

SELECT
    organization_id,
    organization_code,
    legal_entity_id,
    effective_start_date,
    effective_end_date
FROM hr_all_organization_units_f
ORDER BY organization_id;

No tables match your search.