OE_ORDER_HEADERS_ALL: The table used by Oracle Fusion to store the Sales Order Header level data. You would need to query OE_ORDER_HEADERS_ALL table if you have implemented O2C Cycle. So you can say that this is the equivalent of table DOO_HEADERS_ALL table in Oracle Fusion. In this post, we will discuss the columns and indexes present on this table but before that a few important points:
1. OE_ORDER_HEADERS_ALL is the parent table of OE_ORDER_LINES_ALL table which is used to store the Sales Order Line level Data in Oracle Fusion.
2. To connect the Sales Order Header and SO Line table, use the column HEADER_ID.
3. Watch out for the column VERSION_NUMBER as the same ORDER_NUMBER can have multiple versions.
4. Want to find out the Business Unit Name? Connect this table with HR_OPERATING_UNITS using Org_id.
So let’s discuss the table details now.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
OE_ORDER_HEADERS_ALL: Table Structure
| Column Name | Column Type | Nullable |
| HEADER_ID | NUMBER | N |
| ORG_ID | NUMBER | Y |
| ORDER_TYPE_ID | NUMBER | N |
| ORDER_NUMBER | NUMBER | N |
| VERSION_NUMBER | NUMBER | N |
| EXPIRATION_DATE | DATE | Y |
| ORDER_SOURCE_ID | NUMBER | Y |
| SOURCE_DOCUMENT_TYPE_ID | NUMBER | Y |
| ORIG_SYS_DOCUMENT_REF | VARCHAR2(50) | Y |
| SOURCE_DOCUMENT_ID | NUMBER | Y |
| ORDERED_DATE | DATE | Y |
| REQUEST_DATE | DATE | Y |
| PRICING_DATE | DATE | Y |
| SHIPMENT_PRIORITY_CODE | VARCHAR2(30) | Y |
| DEMAND_CLASS_CODE | VARCHAR2(30) | Y |
| PRICE_LIST_ID | NUMBER | Y |
| TAX_EXEMPT_FLAG | VARCHAR2(1) | Y |
| TAX_EXEMPT_NUMBER | VARCHAR2(80) | Y |
| TAX_EXEMPT_REASON_CODE | VARCHAR2(30) | Y |
| CONVERSION_RATE | NUMBER | Y |
| CONVERSION_TYPE_CODE | VARCHAR2(30) | Y |
| CONVERSION_RATE_DATE | DATE | Y |
| PARTIAL_SHIPMENTS_ALLOWED | VARCHAR2(1) | Y |
| SHIP_TOLERANCE_ABOVE | NUMBER | Y |
| SHIP_TOLERANCE_BELOW | NUMBER | Y |
| TRANSACTIONAL_CURR_CODE | VARCHAR2(15) | Y |
| AGREEMENT_ID | NUMBER | Y |
| TAX_POINT_CODE | VARCHAR2(30) | Y |
| CUST_PO_NUMBER | VARCHAR2(50) | Y |
| INVOICING_RULE_ID | NUMBER | Y |
| ACCOUNTING_RULE_ID | NUMBER | Y |
| PAYMENT_TERM_ID | NUMBER | Y |
| SHIPPING_METHOD_CODE | VARCHAR2(30) | Y |
| FREIGHT_CARRIER_CODE | VARCHAR2(30) | Y |
| FOB_POINT_CODE | VARCHAR2(30) | Y |
| FREIGHT_TERMS_CODE | VARCHAR2(30) | Y |
| SOLD_FROM_ORG_ID | NUMBER | Y |
| SOLD_TO_ORG_ID | NUMBER | Y |
| SHIP_FROM_ORG_ID | NUMBER | Y |
| SHIP_TO_ORG_ID | NUMBER | Y |
| INVOICE_TO_ORG_ID | NUMBER | Y |
| DELIVER_TO_ORG_ID | NUMBER | Y |
| SOLD_TO_CONTACT_ID | NUMBER | Y |
| SHIP_TO_CONTACT_ID | NUMBER | Y |
| INVOICE_TO_CONTACT_ID | NUMBER | Y |
| DELIVER_TO_CONTACT_ID | NUMBER | Y |
| CREATION_DATE | DATE | N |
| CREATED_BY | NUMBER | N |
| LAST_UPDATED_BY | NUMBER | N |
| LAST_UPDATE_DATE | DATE | N |
| LAST_UPDATE_LOGIN | NUMBER | Y |
| PROGRAM_APPLICATION_ID | NUMBER | Y |
| PROGRAM_ID | NUMBER | Y |
| PROGRAM_UPDATE_DATE | DATE | Y |
| REQUEST_ID | NUMBER | Y |
| CONTEXT | VARCHAR2(30) | Y |
| ATTRIBUTE1 – ATTRIBUTE20 | VARCHAR2(240) | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2(30) | Y |
| GLOBAL_ATTRIBUTE1 – GLOBAL_ATTRIBUTE20 | VARCHAR2(240) | Y |
| CANCELLED_FLAG | VARCHAR2(1) | Y |
| OPEN_FLAG | VARCHAR2(1) | N |
| BOOKED_FLAG | VARCHAR2(1) | N |
| SALESREP_ID | NUMBER | Y |
| RETURN_REASON_CODE | VARCHAR2(30) | Y |
| ORDER_DATE_TYPE_CODE | VARCHAR2(30) | Y |
| EARLIEST_SCHEDULE_LIMIT | NUMBER | Y |
| LATEST_SCHEDULE_LIMIT | NUMBER | Y |
| PAYMENT_TYPE_CODE | VARCHAR2(30) | Y |
| PAYMENT_AMOUNT | NUMBER | Y |
| CHECK_NUMBER | VARCHAR2(50) | Y |
| CREDIT_CARD_CODE | VARCHAR2(80) | Y |
| CREDIT_CARD_HOLDER_NAME | VARCHAR2(80) | Y |
| CREDIT_CARD_NUMBER | VARCHAR2(80) | Y |
| CREDIT_CARD_EXPIRATION_DATE | DATE | Y |
| CREDIT_CARD_APPROVAL_CODE | VARCHAR2(80) | Y |
| SALES_CHANNEL_CODE | VARCHAR2(30) | Y |
| FIRST_ACK_CODE | VARCHAR2(30) | Y |
| FIRST_ACK_DATE | DATE | Y |
| LAST_ACK_CODE | VARCHAR2(30) | Y |
| LAST_ACK_DATE | DATE | Y |
| ORDER_CATEGORY_CODE | VARCHAR2(30) | N |
| CHANGE_SEQUENCE | VARCHAR2(50) | Y |
| DROP_SHIP_FLAG | VARCHAR2(1) | Y |
| CUSTOMER_PAYMENT_TERM_ID | NUMBER | Y |
| SHIPPING_INSTRUCTIONS | VARCHAR2(2000) | Y |
| PACKING_INSTRUCTIONS | VARCHAR2(2000) | Y |
| TP_CONTEXT | VARCHAR2(30) | Y |
| TP_ATTRIBUTE1 – TP_ATTRIBUTE15 | VARCHAR2(240) | Y |
| FLOW_STATUS_CODE+C113A104:C114AA104:C114 | VARCHAR2(30) | Y |
| MARKETING_SOURCE_CODE_ID | NUMBER | Y |
| CREDIT_CARD_APPROVAL_DATE | DATE | Y |
| UPGRADED_FLAG | VARCHAR2(1) | Y |
| CUSTOMER_PREFERENCE_SET_CODE | VARCHAR2(30) | Y |
| BOOKED_DATE | DATE | Y |
| LOCK_CONTROL | NUMBER | Y |
| PRICE_REQUEST_CODE | VARCHAR2(240) | Y |
| BATCH_ID | NUMBER | Y |
| XML_MESSAGE_ID | NUMBER | Y |
| ACCOUNTING_RULE_DURATION | NUMBER | Y |
| BLANKET_NUMBER | NUMBER | Y |
| SALES_DOCUMENT_TYPE_CODE | VARCHAR2(30) | Y |
| SOLD_TO_PHONE_ID | NUMBER | Y |
| FULFILLMENT_SET_NAME | VARCHAR2(30) | Y |
| LINE_SET_NAME | VARCHAR2(30) | Y |
| DEFAULT_FULFILLMENT_SET | VARCHAR2(1) | Y |
| TRANSACTION_PHASE_CODE | VARCHAR2(30) | Y |
| SALES_DOCUMENT_NAME | VARCHAR2(240) | Y |
| QUOTE_NUMBER | NUMBER | Y |
| QUOTE_DATE | DATE | Y |
| USER_STATUS_CODE | VARCHAR2(30) | Y |
| DRAFT_SUBMITTED_FLAG | VARCHAR2(1) | Y |
| SOURCE_DOCUMENT_VERSION_NUMBER | NUMBER | Y |
| SOLD_TO_SITE_USE_ID | NUMBER | Y |
| SUPPLIER_SIGNATURE | VARCHAR2(240) | Y |
| SUPPLIER_SIGNATURE_DATE | DATE | Y |
| CUSTOMER_SIGNATURE | VARCHAR2(240) | Y |
| CUSTOMER_SIGNATURE_DATE | DATE | Y |
| MINISITE_ID | NUMBER | Y |
| END_CUSTOMER_ID | NUMBER | Y |
| END_CUSTOMER_CONTACT_ID | NUMBER | Y |
| END_CUSTOMER_SITE_USE_ID | NUMBER | Y |
| IB_OWNER | VARCHAR2(60) | Y |
| IB_CURRENT_LOCATION | VARCHAR2(60) | Y |
| IB_INSTALLED_AT_LOCATION | VARCHAR2(60) | Y |
| ORDER_FIRMED_DATE | DATE | Y |
| INST_ID | NUMBER | Y |
| IB_INSTALLED_AT_LOCATION#1 | VARCHAR2(80) | Y |
| IB_CURRENT_LOCATION#1 | VARCHAR2(80) | Y |
| CSR_USER_ID | NUMBER | Y |
| CANCEL_UNSHIPPED_LINES | VARCHAR2(1) | Y |
Indexes
| Index Name | Type | Uniqueness? | Column Name |
| OE_ORDER_HEADERS_N1 | NORMAL | NONUNIQUE | BLANKET_NUMBER |
| OE_ORDER_HEADERS_N2 | NORMAL | NONUNIQUE | SOLD_TO_ORG_ID |
| OE_ORDER_HEADERS_N3 | NORMAL | NONUNIQUE | SHIP_TO_ORG_ID |
| OE_ORDER_HEADERS_N4 | NORMAL | NONUNIQUE | INVOICE_TO_ORG_ID |
| OE_ORDER_HEADERS_N5 | NORMAL | NONUNIQUE | CUST_PO_NUMBER |
| OE_ORDER_HEADERS_N6 | NORMAL | NONUNIQUE | BATCH_ID |
| OE_ORDER_HEADERS_N7 | NORMAL | NONUNIQUE | ORIG_SYS_DOCUMENT_REF |
| OE_ORDER_HEADERS_N7 | NORMAL | NONUNIQUE | ORDER_SOURCE_ID |
| OE_ORDER_HEADERS_N8 | NORMAL | NONUNIQUE | OPEN_FLAG |
| OE_ORDER_HEADERS_N9 | NORMAL | NONUNIQUE | QUOTE_NUMBER |
| OE_ORDER_HEADERS_N9 | NORMAL | NONUNIQUE | ORDER_TYPE_ID |
| OE_ORDER_HEADERS_U1 | NORMAL | UNIQUE | HEADER_ID |
| OE_ORDER_HEADERS_U2 | NORMAL | UNIQUE | ORDER_NUMBER |
| OE_ORDER_HEADERS_U2 | NORMAL | UNIQUE | ORDER_TYPE_ID |
| OE_ORDER_HEADERS_U2 | NORMAL | UNIQUE | VERSION_NUMBER |
| OE_ORDER_LINES6_ENDECA_N1 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
