In Oracle Fusion, the DOO_HEADERS_ALL table plays a significant role in managing and tracking order headers within the Order Orchestration (DOO) module. This table acts as a central repository for storing essential information related to order processing and management. In this blog post, we will delve into the functionality of the DOO_HEADERS_ALL table, understand what it stores, and explore its important linking tables within the Oracle Fusion ecosystem.
For sample SQLs check DOO_HEADERS_ALL SQL query
Table of Contents
Functionality and Data Storage:
The DOO_HEADERS_ALL table is designed to store the primary data associated with order headers. It captures information about various types of orders, such as sales orders, purchase orders, work orders, and transfer orders, among others. The table maintains a comprehensive record of each order header, enabling organizations to efficiently manage their order processing workflows.
Key data stored in the DOO_HEADERS_ALL table includes the order header ID, order number, order type, order status, customer details, requested ship date, bill-to and ship-to locations, payment terms, and other relevant attributes. Additionally, the table maintains effective-dated records, enabling historical tracking of order header changes over time.
Linking Tables to DOO_HEADERS_ALL:
To provide a complete view of order management, the DOO_HEADERS_ALL table is linked to various important tables within Oracle Fusion. Let’s explore some of these linking tables:
DOO_HEADERS_ALL table and the linking tables mentioned:
- DOO_LINES_ALL:
- Linking Column: HEADER_ID
- Description: The HEADER_ID column in the DOO_LINES_ALL table is used to establish a relationship between order headers in the DOO_HEADERS_ALL table and their corresponding order lines.
- HZ_PARTIES:
- Linking Column: SOLD_TO_PARTY_ID
- Description: The SOLD_TO_PARTY_ID column in the DOO_HEADERS_ALL table links to the PARTY_ID column in the HZ_PARTIES table, associating order headers with specific customers.
- HZ_LOCATIONS:
- Linking Column: SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID
- Description: The SHIP_TO_LOCATION_ID and BILL_TO_LOCATION_ID columns in the DOO_HEADERS_ALL table link to the LOCATION_ID column in the HZ_LOCATIONS table. These columns store information about the ship-to and bill-to locations associated with each order header.
- AR_PAYMENT_SCHEDULES_ALL:
- Linking Column: HEADER_ID
- Description: The HEADER_ID column in the DOO_HEADERS_ALL table is used to link order headers with their corresponding payment schedules in the AR_PAYMENT_SCHEDULES_ALL table. This column facilitates the capture of payment-related details for orders.
- GL_CODE_COMBINATIONS:
- Linking Column: CODE_COMBINATION_ID use this as in between table .
These linking column names establish relationships between the DOO_HEADERS_ALL table and the respective linking tables, enabling seamless integration and retrieval of data across Oracle Fusion modules for efficient order management.
Details
- Schema: FUSION
- Object owner: DOO
- Object type: TABLE
- Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
| DOO_HEADERS_ALL_PK | HEADER_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| HEADER_ID | NUMBER | 18 | Yes | Unique identifier of the orchestration order. | |
| INTERNAL_COMMENTS | VARCHAR2 | 4000 | This is an internal column that Oracle uses to monitor data for an item when processing a sales order. Don’t use it for any other reason. | ||
| AGREEMENT_HEADER_ID | NUMBER | 18 | Value that uniquely identifies the sales agreement on the order header. | ||
| AGREEMENT_VERSION_NUMBER | NUMBER | 18 | Version number of the sales agreement. | ||
| PRE_CREDIT_CHECKED_FLAG | VARCHAR2 | 1 | The attribute is used to indicate if an Order has already been credit checked within upstream channel systems or not. | ||
| ORDER_NUMBER | VARCHAR2 | 50 | Yes | Representation of a sales order used in communication with fulfillment systems. | |
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| OWNER_ID | NUMBER | 18 | DOO user who is responsible for the orchestration order | ||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| SOURCE_ORDER_SYSTEM | VARCHAR2 | 50 | Yes | Reference to the order capture system that the source order came from. | |
| SOURCE_ORDER_NUMBER | VARCHAR2 | 50 | Yes | Order Capture order number that is the origin of the contract line. | |
| SOURCE_ORDER_ID | VARCHAR2 | 50 | Yes | Reference to the unique identifier for the source order in order capture | |
| SOURCE_DOCUMENT_TYPE_CODE | VARCHAR2 | 50 | Indicates the Original Document document type on the order | ||
| SOURCE_REVISION_NUMBER | NUMBER | 18 | Yes | Reference to the revision number of the source order from order capture. | |
| ORIG_SYS_DOCUMENT_REF | VARCHAR2 | 50 | Original document reference for the orchestration order in external system | ||
| SOURCE_ORG_ID | NUMBER | 18 | Reference to the unique identifier for the organization of the source order. | ||
| ORG_ID | NUMBER | 18 | Yes | Indicates the identifier of the business unit associated to the row. | |
| LEGAL_ENTITY_ID | NUMBER | 18 | Reference to the unique identifier for the legal entity | ||
| SOLD_TO_CUSTOMER_ID | NUMBER | 18 | Reference to the unique identifier for the sold-to customer | ||
| SOLD_TO_CONTACT_ID | NUMBER | 18 | Reference to the unique identifier for the sold-to contact | ||
| ORDERED_DATE | DATE | Yes | Date when the items where ordered | ||
| CUSTOMER_PO_NUMBER | VARCHAR2 | 50 | User defined identifier for a purchase order. | ||
| ORDER_TYPE_CODE | VARCHAR2 | 30 | Name of the type of order that was placed. | ||
| TRANSACTIONAL_CURRENCY_CODE | VARCHAR2 | 15 | Currency code captured at the order header level. | ||
| CONVERSION_RATE | NUMBER | Foreign exchange conversion rate used to convert the transactional currency into the standardized currency in the UI. | |||
| CONVERSION_TYPE_CODE | VARCHAR2 | 30 | Describes how the conversion rate was computed | ||
| CONVERSION_DATE | DATE | Date that the foreign exchange rate was valid | |||
| STATUS_CODE | VARCHAR2 | 30 | Yes | Status of the orchestration order | |
| PARTIAL_SHIP_ALLOWED_FLAG | VARCHAR2 | 1 | Indicate if the shipment can be split | ||
| OPEN_FLAG | VARCHAR2 | 1 | Yes | Indicates that the status of an order that is not closed or canceled. | |
| ON_HOLD | VARCHAR2 | 1 | Indicates an order is not accepting further processing. | ||
| CANCELED_FLAG | VARCHAR2 | 1 | Indicates that the order is canceled. | ||
| CHANGE_VERSION_NUMBER | NUMBER | 18 | Not used. | ||
| IS_EDITABLE | VARCHAR2 | 1 | Used by change processing framework for locking the orchestration order from additional change requests | ||
| CANCEL_REASON_CODE | VARCHAR2 | 30 | Reason for cancellation of the order | ||
| SOLD_TO_PARTY_ID | NUMBER | 18 | Reference to the unique identifier for the sold-to Party. | ||
| SOLD_TO_PARTY_CONTACT_ID | NUMBER | 18 | Reference to the unique identifier for the sold-to Party Contact. | ||
| REQUEST_CANCEL_DATE | TIMESTAMP | Date on which customer requested Cancellation on line. | |||
| COMMENTS | VARCHAR2 | 2000 | Comments for the line | ||
| ALLOW_CURRENCY_OVERRIDE_FLAG | VARCHAR2 | 1 | Indicates whether the currency can be overridden | ||
| APPLIED_CURRENCY_CODE | VARCHAR2 | 15 | Currency Code applied by Pricing | ||
| PRICED_ON | TIMESTAMP | The date when the document is priced. This column will be populated with the system date at the beginning of a pricing process | |||
| PRICING_SEGMENT_CODE | VARCHAR2 | 30 | Stores either the pricing segment code passed in from the caller or the pricing segment code derived by the pricing engine | ||
| PRICING_SEGMENT_EXPLANATION | VARCHAR2 | 1000 | Message explaining why the Pricing Segment was applied | ||
| PRICING_STRATEGY_EXPLANATION | VARCHAR2 | 1000 | Message explaining why the Pricing Strategy was applied | ||
| SEGMENT_EXPLANATION_MSG_NAME | VARCHAR2 | 30 | Stores name of the Pricing Segment Strategy Message. The message name can be used to get the message text from the pricer in the session language | ||
| STRATEGY_EXPLANATION_MSG_NAME | VARCHAR2 | 30 | Stores name of the Pricing Strategy Message. The message name can be used to get the message text from the pricer in the session language | ||
| PRICING_STRATEGY_ID | NUMBER | 18 | Stores either the pricing strategy id passed in from the caller or the pricing strategy id derived by the pricing engine | ||
| FREEZE_TAX_FLAG | VARCHAR2 | 1 | Indicates if taxes for the order should be re-calculated or not | ||
| SHIPSET_FLAG | VARCHAR2 | 1 | Group of order lines within an order that need to ship together from their common origin to a common destination and which have the same scheduled ship date. | ||
| FULFILL_ORG_ID | NUMBER | 18 | Reference to the unique identifier for the fulfillment organization. | ||
| SUPPLIER_ID | NUMBER | 18 | This will hold the value for Supplier on whom Purchase Order is created. | ||
| SUPPLIER_SITE_ID | NUMBER | 18 | This will hold the value for Supplier Site on whom Purchase Order is created. | ||
| DEMAND_CLASS_CODE | VARCHAR2 | 30 | Demand Class used by the promise engine to consume target allocation amounts. | ||
| SUBSTITUTE_ALLOWED_FLAG | VARCHAR2 | 1 | Indicates that items on the fulfillment line can be substituted for various reasons like shortage of material, or as a goodwill gesture to the customer. | ||
| FREIGHT_TERMS_CODE | VARCHAR2 | 30 | Freight Terms, specifying the responsibility for Freight charges | ||
| FOB_POINT_CODE | VARCHAR2 | 30 | Code that indicates freight on board | ||
| PACKING_INSTRUCTIONS | VARCHAR2 | 1000 | A document that contains information concerning the packing of an item. | ||
| SHIPPING_INSTRUCTIONS | VARCHAR2 | 1000 | A message on a document that provides details about cargo and any requirements for its physical movement. | ||
| SHIPMENT_PRIORITY_CODE | VARCHAR2 | 30 | Indicates the priority of the fulfillment lines to be shipped. | ||
| REQUEST_SHIP_DATE | DATE | The original date the customer requested the item ship on. | |||
| LATEST_ACCEPTABLE_SHIP_DATE | DATE | The latest date that the customer is willing to have a warehouse ship a product. | |||
| EARLIEST_ACCEPTABLE_SHIP_DATE | DATE | Date defining the earliest that a customer is willing to receive a shipment. | |||
| CARRIER_ID | NUMBER | 18 | Reference to the unique identifier for the carrier. | ||
| SHIP_MODE_OF_TRANSPORT | VARCHAR2 | 30 | Type of transportation used to ship the product, such as truck, air or boat. | ||
| SHIP_CLASS_OF_SERVICE | VARCHAR2 | 30 | Priority of transportation that affects how quickly goods are transported | ||
| REQUEST_ARRIVAL_DATE | DATE | The original date the customer requested the item arrive on. | |||
| LATEST_ACCEPT_ARRIVAL_DATE | DATE | The latest date that the customer is willing to receive a product. | |||
| EARLIEST_ACCEPT_ARRIVAL_DATE | DATE | The earliest date that the customer is willing to receive a product. | |||
| SUBMITTED_FLAG | VARCHAR2 | 1 | This flag tracks the state of an order. ‘N’ reflects that the order is not yet submitted, ‘Y’ reflects that the order has been validated and submitted. | ||
| REFERENCE_HEADER_ID | NUMBER | 18 | reference header_id for cloning | ||
| FREEZE_PRICE_FLAG | VARCHAR2 | 1 | Indicates if the order should be priced or not. | ||
| FREEZE_SHIPPING_CHARGE_FLAG | VARCHAR2 | 1 | Indicates if shipping charges for the order should be recalculated or not. | ||
| PAYMENT_TERM_ID | NUMBER | 18 | Refers to the unique identifier for the payment term. | ||
| SOLD_TO_PARTY_CONTACT_POINT_ID | NUMBER | 18 | Refers to the unique identifier for the sold-to party contact point | ||
| MODIFIED_FLAG | VARCHAR2 | 1 | Indicates that the row has been modified in the version currently submitted | ||
| REVISION_SOURCE_ORDER_SYSTEM | VARCHAR2 | 30 | This attribute column tracks the source system which is revising the order | ||
| APPROVAL_SEQUENCE_NUMBER | NUMBER | 18 | Tracks the sequence number of the request sent to the Approval Management system for this order. This column, combined with HEADER_ID provides the unique identifier used for interactions with Approval Management System. | ||
| TRADE_COMPLIANCE_RESULT_CODE | VARCHAR2 | 30 | Roll-up result of the trade compliance screening for the lines | ||
| CREATED_IN_RELEASE | VARCHAR2 | 15 | Stores the release version when order was created | ||
| SUBMITTED_DATE | TIMESTAMP | The date and time of the submission of the order. | |||
| SUBMITTED_BY | VARCHAR2 | 64 | The user who submitted the order. | ||
| SALES_CHANNEL_CODE | VARCHAR2 | 30 | Name of the type of Sales Channel that was used like Web,Phone, CPQ, webstore, etc. | ||
| SALESPERSON_ID | NUMBER | 18 | Primary Sales person identifier. | ||
| BATCH_ID | NUMBER | 18 | Unique identifier of the batch that was used for importing the order. | ||
| CREATION_MODE | VARCHAR2 | 30 | Identifies the interface using which the order was captured. | ||
| MDO_FLAG | VARCHAR2 | 1 | Determines the mode of orchestration e.g., ‘Y’ is MDO , ‘N’ is classic. | ||
| SUBINVENTORY | VARCHAR2 | 10 | Identifies the Physical or logical location of the item. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| doo_orchestration_groups | doo_headers_all | HEADER_ID |
| doo_hold_instances | doo_headers_all | DOO_HEADER_ID |
| doo_lines_all | doo_headers_all | HEADER_ID |
| doo_aggregation_task_instances | doo_headers_all | HEADER_ID |
| doo_order_states | doo_headers_all | HEADER_ID |
| doo_document_references | doo_headers_all | HEADER_ID |
| doo_sales_credits | doo_headers_all | HEADER_ID |
| doo_task_batch_requests | doo_headers_all | HEADER_ID |
| doo_headers_eff_b | doo_headers_all | HEADER_ID |
| doo_message_summary_statuses | doo_headers_all | SUMMARY_ID |
| doo_order_terms | doo_headers_all | HEADER_ID |
| doo_source_orders | doo_headers_all | HEADER_ID |
| doo_error_recovery_instances | doo_headers_all | HEADER_ID |
| doo_ui_recent_objects | doo_headers_all | HEADER_ID |
| doo_order_totals | doo_headers_all | HEADER_ID |
| doo_order_addresses | doo_headers_all | HEADER_ID |
| doo_price_adjustments | doo_headers_all | HEADER_ID |
| doo_payments | doo_headers_all | HEADER_ID |
| doo_order_incentives | doo_headers_all | HEADER_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| DOO_HEADERS_ALL_FK1 | Non Unique | Default | SOLD_TO_CUSTOMER_ID |
| DOO_HEADERS_ALL_FK2 | Non Unique | Default | UPPER(“CUSTOMER_PO_NUMBER”) |
| DOO_HEADERS_ALL_FK3 | Non Unique | Default | SOLD_TO_PARTY_ID |
| DOO_HEADERS_ALL_N1 | Non Unique | Default | SOURCE_ORDER_NUMBER, SOURCE_ORDER_SYSTEM |
| DOO_HEADERS_ALL_N2 | Non Unique | Default | OPEN_FLAG, ORG_ID, SOURCE_ORDER_SYSTEM, HEADER_ID |
| DOO_HEADERS_ALL_N3 | Non Unique | Default | UPPER(“ORDER_NUMBER”) |
| DOO_HEADERS_ALL_N4 | Non Unique | Default | UPPER(“SOURCE_ORDER_NUMBER”) |
| DOO_HEADERS_ALL_N5 | Non Unique | Default | “SOURCE_ORDER_SYSTEM” || ‘:’ || “SOURCE_ORDER_ID” |
| DOO_HEADERS_ALL_N6 | Non Unique | Default | ORDERED_DATE |
| DOO_HEADERS_ALL_PK | Unique | Default | HEADER_ID |
| DOO_HEADERS_ALL_UK1 | Unique | Default | SOURCE_ORDER_ID, SOURCE_ORDER_SYSTEM, SUBMITTED_FLAG, CHANGE_VERSION_NUMBER |
| DOO_HEADERS_ALL_UK2 | Unique | Default | ORDER_NUMBER, SOURCE_ORDER_SYSTEM, SUBMITTED_FLAG, CHANGE_VERSION_NUMBER |
Conclusion:
The DOO_HEADERS_ALL table in Oracle Fusion is a crucial component for effective order management and tracking within the Order Orchestration module. It stores essential data related to order headers and effectively linking to other tables, it enables organizations to streamline their order processing workflows, gain insights into customer behavior, and facilitate accurate financial tracking. Understanding the functionality of the DOO_HEADERS_ALL table and its linking tables is essential for optimizing order management processes and leveraging the full potential of Oracle Fusion’s capabilities.
