DOO_HEADERS_ALL Table in Oracle Fusion and Linking objects

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

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.

image 15 DOO_HEADERS_ALL 1

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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

NameColumns
DOO_HEADERS_ALL_PKHEADER_ID

Columns

NameDatatypeLengthPrecisionNot-nullComments
HEADER_IDNUMBER18YesUnique identifier of the orchestration order.
INTERNAL_COMMENTSVARCHAR24000This 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_IDNUMBER18Value that uniquely identifies the sales agreement on the order header.
AGREEMENT_VERSION_NUMBERNUMBER18Version number of the sales agreement.
PRE_CREDIT_CHECKED_FLAGVARCHAR21The attribute is used to indicate if an Order has already been credit checked within upstream channel systems or not.
ORDER_NUMBERVARCHAR250YesRepresentation of a sales order used in communication with fulfillment systems.
OBJECT_VERSION_NUMBERNUMBER9YesUsed 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_IDNUMBER18DOO user who is responsible for the orchestration order
CREATION_DATETIMESTAMPYesWho column: indicates the date and time of the creation of the row.
CREATED_BYVARCHAR264YesWho column: indicates the user who created the row.
LAST_UPDATE_DATETIMESTAMPYesWho column: indicates the date and time of the last update of the row.
LAST_UPDATED_BYVARCHAR264YesWho column: indicates the user who last updated the row.
LAST_UPDATE_LOGINVARCHAR232Who column: indicates the session login associated to the user who last updated the row.
SOURCE_ORDER_SYSTEMVARCHAR250YesReference to the order capture system that the source order came from.
SOURCE_ORDER_NUMBERVARCHAR250YesOrder Capture order number that is the origin of the contract line.
SOURCE_ORDER_IDVARCHAR250YesReference to the unique identifier for the source order in order capture
SOURCE_DOCUMENT_TYPE_CODEVARCHAR250Indicates the Original Document document type on the order
SOURCE_REVISION_NUMBERNUMBER18YesReference to the revision number of the source order from order capture.
ORIG_SYS_DOCUMENT_REFVARCHAR250Original document reference for the orchestration order in external system
SOURCE_ORG_IDNUMBER18Reference to the unique identifier for the organization of the source order.
ORG_IDNUMBER18YesIndicates the identifier of the business unit associated to the row.
LEGAL_ENTITY_IDNUMBER18Reference to the unique identifier for the legal entity
SOLD_TO_CUSTOMER_IDNUMBER18Reference to the unique identifier for the sold-to customer
SOLD_TO_CONTACT_IDNUMBER18Reference to the unique identifier for the sold-to contact
ORDERED_DATEDATEYesDate when the items where ordered
CUSTOMER_PO_NUMBERVARCHAR250User defined identifier for a purchase order.
ORDER_TYPE_CODEVARCHAR230Name of the type of order that was placed.
TRANSACTIONAL_CURRENCY_CODEVARCHAR215Currency code captured at the order header level.
CONVERSION_RATENUMBERForeign exchange conversion rate used to convert the transactional currency into the standardized currency in the UI.
CONVERSION_TYPE_CODEVARCHAR230Describes how the conversion rate was computed
CONVERSION_DATEDATEDate that the foreign exchange rate was valid
STATUS_CODEVARCHAR230YesStatus of the orchestration order
PARTIAL_SHIP_ALLOWED_FLAGVARCHAR21Indicate if the shipment can be split
OPEN_FLAGVARCHAR21YesIndicates that the status of an order that is not closed or canceled.
ON_HOLDVARCHAR21Indicates an order is not accepting further processing.
CANCELED_FLAGVARCHAR21Indicates that the order is canceled.
CHANGE_VERSION_NUMBERNUMBER18Not used.
IS_EDITABLEVARCHAR21Used by change processing framework for locking the orchestration order from additional change requests
CANCEL_REASON_CODEVARCHAR230Reason for cancellation of the order
SOLD_TO_PARTY_IDNUMBER18Reference to the unique identifier for the sold-to Party.
SOLD_TO_PARTY_CONTACT_IDNUMBER18Reference to the unique identifier for the sold-to Party Contact.
REQUEST_CANCEL_DATETIMESTAMPDate on which customer requested Cancellation on line.
COMMENTSVARCHAR22000Comments for the line
ALLOW_CURRENCY_OVERRIDE_FLAGVARCHAR21Indicates whether the currency can be overridden
APPLIED_CURRENCY_CODEVARCHAR215Currency Code applied by Pricing
PRICED_ONTIMESTAMPThe date when the document is priced. This column will be populated with the system date at the beginning of a pricing process
PRICING_SEGMENT_CODEVARCHAR230Stores either the pricing segment code passed in from the caller or the pricing segment code derived by the pricing engine
PRICING_SEGMENT_EXPLANATIONVARCHAR21000Message explaining why the Pricing Segment was applied
PRICING_STRATEGY_EXPLANATIONVARCHAR21000Message explaining why the Pricing Strategy was applied
SEGMENT_EXPLANATION_MSG_NAMEVARCHAR230Stores 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_NAMEVARCHAR230Stores 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_IDNUMBER18Stores either the pricing strategy id passed in from the caller or the pricing strategy id derived by the pricing engine
FREEZE_TAX_FLAGVARCHAR21Indicates if taxes for the order should be re-calculated or not
SHIPSET_FLAGVARCHAR21Group 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_IDNUMBER18Reference to the unique identifier for the fulfillment organization.
SUPPLIER_IDNUMBER18This will hold the value for Supplier on whom Purchase Order is created.
SUPPLIER_SITE_IDNUMBER18This will hold the value for Supplier Site on whom Purchase Order is created.
DEMAND_CLASS_CODEVARCHAR230Demand Class used by the promise engine to consume target allocation amounts.
SUBSTITUTE_ALLOWED_FLAGVARCHAR21Indicates 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_CODEVARCHAR230Freight Terms, specifying the responsibility for Freight charges
FOB_POINT_CODEVARCHAR230Code that indicates freight on board
PACKING_INSTRUCTIONSVARCHAR21000A document that contains information concerning the packing of an item.
SHIPPING_INSTRUCTIONSVARCHAR21000A message on a document that provides details about cargo and any requirements for its physical movement.
SHIPMENT_PRIORITY_CODEVARCHAR230Indicates the priority of the fulfillment lines to be shipped.
REQUEST_SHIP_DATEDATEThe original date the customer requested the item ship on.
LATEST_ACCEPTABLE_SHIP_DATEDATEThe latest date that the customer is willing to have a warehouse ship a product.
EARLIEST_ACCEPTABLE_SHIP_DATEDATEDate defining the earliest that a customer is willing to receive a shipment.
CARRIER_IDNUMBER18Reference to the unique identifier for the carrier.
SHIP_MODE_OF_TRANSPORTVARCHAR230Type of transportation used to ship the product, such as truck, air or boat.
SHIP_CLASS_OF_SERVICEVARCHAR230Priority of transportation that affects how quickly goods are transported
REQUEST_ARRIVAL_DATEDATEThe original date the customer requested the item arrive on.
LATEST_ACCEPT_ARRIVAL_DATEDATEThe latest date that the customer is willing to receive a product.
EARLIEST_ACCEPT_ARRIVAL_DATEDATEThe earliest date that the customer is willing to receive a product.
SUBMITTED_FLAGVARCHAR21This 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_IDNUMBER18reference header_id for cloning
FREEZE_PRICE_FLAGVARCHAR21Indicates if the order should be priced or not.
FREEZE_SHIPPING_CHARGE_FLAGVARCHAR21Indicates if shipping charges for the order should be recalculated or not.
PAYMENT_TERM_IDNUMBER18Refers to the unique identifier for the payment term.
SOLD_TO_PARTY_CONTACT_POINT_IDNUMBER18Refers to the unique identifier for the sold-to party contact point
MODIFIED_FLAGVARCHAR21Indicates that the row has been modified in the version currently submitted
REVISION_SOURCE_ORDER_SYSTEMVARCHAR230This attribute column tracks the source system which is revising the order
APPROVAL_SEQUENCE_NUMBERNUMBER18Tracks 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_CODEVARCHAR230Roll-up result of the trade compliance screening for the lines
CREATED_IN_RELEASEVARCHAR215Stores the release version when order was created
SUBMITTED_DATETIMESTAMPThe date and time of the submission of the order.
SUBMITTED_BYVARCHAR264The user who submitted the order.
SALES_CHANNEL_CODEVARCHAR230Name of the type of Sales Channel that was used like Web,Phone, CPQ, webstore, etc.
SALESPERSON_IDNUMBER18Primary Sales person identifier.
BATCH_IDNUMBER18Unique identifier of the batch that was used for importing the order.
CREATION_MODEVARCHAR230Identifies the interface using which the order was captured.
MDO_FLAGVARCHAR21Determines the mode of orchestration e.g., โ€˜Yโ€™ is MDO , โ€˜Nโ€™ is classic.
SUBINVENTORYVARCHAR210Identifies the Physical or logical location of the item.

Foreign Keys

TableForeign TableForeign Key Column
doo_orchestration_groupsdoo_headers_allHEADER_ID
doo_hold_instancesdoo_headers_allDOO_HEADER_ID
doo_lines_alldoo_headers_allHEADER_ID
doo_aggregation_task_instancesdoo_headers_allHEADER_ID
doo_order_statesdoo_headers_allHEADER_ID
doo_document_referencesdoo_headers_allHEADER_ID
doo_sales_creditsdoo_headers_allHEADER_ID
doo_task_batch_requestsdoo_headers_allHEADER_ID
doo_headers_eff_bdoo_headers_allHEADER_ID
doo_message_summary_statusesdoo_headers_allSUMMARY_ID
doo_order_termsdoo_headers_allHEADER_ID
doo_source_ordersdoo_headers_allHEADER_ID
doo_error_recovery_instancesdoo_headers_allHEADER_ID
doo_ui_recent_objectsdoo_headers_allHEADER_ID
doo_order_totalsdoo_headers_allHEADER_ID
doo_order_addressesdoo_headers_allHEADER_ID
doo_price_adjustmentsdoo_headers_allHEADER_ID
doo_paymentsdoo_headers_allHEADER_ID
doo_order_incentivesdoo_headers_allHEADER_ID

Indexes

IndexUniquenessTablespaceColumns
DOO_HEADERS_ALL_FK1Non UniqueDefaultSOLD_TO_CUSTOMER_ID
DOO_HEADERS_ALL_FK2Non UniqueDefaultUPPER(โ€œCUSTOMER_PO_NUMBERโ€)
DOO_HEADERS_ALL_FK3Non UniqueDefaultSOLD_TO_PARTY_ID
DOO_HEADERS_ALL_N1Non UniqueDefaultSOURCE_ORDER_NUMBER, SOURCE_ORDER_SYSTEM
DOO_HEADERS_ALL_N2Non UniqueDefaultOPEN_FLAG, ORG_ID, SOURCE_ORDER_SYSTEM, HEADER_ID
DOO_HEADERS_ALL_N3Non UniqueDefaultUPPER(โ€œORDER_NUMBERโ€)
DOO_HEADERS_ALL_N4Non UniqueDefaultUPPER(โ€œSOURCE_ORDER_NUMBERโ€)
DOO_HEADERS_ALL_N5Non UniqueDefaultโ€œSOURCE_ORDER_SYSTEMโ€ || โ€˜:โ€™ || โ€œSOURCE_ORDER_IDโ€
DOO_HEADERS_ALL_N6Non UniqueDefaultORDERED_DATE
DOO_HEADERS_ALL_PKUniqueDefaultHEADER_ID
DOO_HEADERS_ALL_UK1UniqueDefaultSOURCE_ORDER_ID, SOURCE_ORDER_SYSTEM, SUBMITTED_FLAG, CHANGE_VERSION_NUMBER
DOO_HEADERS_ALL_UK2UniqueDefaultORDER_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.

Call web service from fusion page

wpsbutton
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart