Doo_lines_all is the table Oracle Fusion uses to store the sales order line level data. DOO_HEADERS_ALL is the parent table which holds the sales order header details and is one of the most important table of O2C Cycle. Primary key of this table is LINE_ID. while you can use header_id to connect it to doo_headers_all table. We’ll see the table structure, indexes and constraints of this table in this post.
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...
Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| HEADER_ID | NUMBER | N |
| LINE_ID | NUMBER | N |
| LINE_NUMBER | NUMBER | N |
| STATUS_CODE | VARCHAR2(120) | N |
| OWNER_ID | NUMBER | Y |
| CREATION_DATE | TIMESTAMP(6) | N |
| CREATED_BY | VARCHAR2(256) | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| SOURCE_LINE_ID | VARCHAR2(200) | N |
| SOURCE_LINE_NUMBER | VARCHAR2(400) | N |
| SOURCE_ORDER_ID | VARCHAR2(200) | N |
| SOURCE_ORDER_NUMBER | VARCHAR2(200) | N |
| SOURCE_ORDER_SYSTEM | VARCHAR2(120) | N |
| SOURCE_SCHEDULE_ID | VARCHAR2(200) | N |
| SOURCE_SCHEDULE_NUMBER | VARCHAR2(200) | N |
| SOURCE_REVISION_NUMBER | NUMBER | N |
| ITEM_TYPE_CODE | VARCHAR2(120) | N |
| ORDERED_QTY | NUMBER | N |
| CANCELED_QTY | NUMBER | Y |
| ORDERED_UOM | VARCHAR2(12) | N |
| SOURCE_ORG_ID | NUMBER | Y |
| ORG_ID | NUMBER | N |
| ACTUAL_SHIP_DATE | DATE | Y |
| SCHEDULE_SHIP_DATE | DATE | Y |
| RMA_DELIVERED_QTY | NUMBER | Y |
| EXTENDED_AMOUNT | NUMBER | Y |
| FULFILLED_QTY | NUMBER | Y |
| FULFILLMENT_DATE | DATE | Y |
| LINE_TYPE_CODE | VARCHAR2(120) | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| OPEN_FLAG | VARCHAR2(4) | N |
| CANCELED_FLAG | VARCHAR2(4) | Y |
| INVENTORY_ITEM_ID | NUMBER | N |
| COMP_SEQ_PATH | VARCHAR2(4000) | Y |
| PARENT_LINE_ID | NUMBER | Y |
| ORIG_SYS_DOCUMENT_REF | VARCHAR2(200) | Y |
| ORIG_SYS_DOCUMENT_LINE_REF | VARCHAR2(200) | Y |
| ROOT_PARENT_LINE_ID | NUMBER | Y |
| SHIPPED_QTY | NUMBER | Y |
| UNIT_LIST_PRICE | NUMBER | Y |
| UNIT_SELLING_PRICE | NUMBER | Y |
| DELTA_TYPE | NUMBER | Y |
| REFERENCE_LINE_ID | NUMBER | Y |
| ON_HOLD | VARCHAR2(4) | Y |
| INVENTORY_ORGANIZATION_ID | NUMBER | N |
| CATEGORY_CODE | VARCHAR2(120) | N |
| TRANSFORM_FROM_LINE_ID | NUMBER | Y |
| ITEM_SUB_TYPE_CODE | VARCHAR2(120) | Y |
| QUANTITY_PER_MODEL | NUMBER | Y |
| MODIFIED_FLAG | VARCHAR2(4) | Y |
| DISPLAY_LINE_NUMBER | VARCHAR2(400) | Y |
| CREATED_IN_RELEASE | VARCHAR2(60) | Y |
Index
| INDEX NAME | INDEX TYPE | UNIQUENESS | COLUMN NAME |
| DOO_LINES_ALL_FK1 | NORMAL | NONUNIQUE | HEADER_ID |
| DOO_LINES_ALL_FK2 | NORMAL | NONUNIQUE | INVENTORY_ITEM_ID |
| DOO_LINES_ALL_FK2 | NORMAL | NONUNIQUE | OPEN_FLAG |
| DOO_LINES_ALL_FK3 | NORMAL | NONUNIQUE | PARENT_LINE_ID |
| DOO_LINES_ALL_FK4 | NORMAL | NONUNIQUE | ROOT_PARENT_LINE_ID |
| DOO_LINES_ALL_FK5 | NORMAL | NONUNIQUE | REFERENCE_LINE_ID |
| DOO_LINES_ALL_N1 | NORMAL | NONUNIQUE | SOURCE_SCHEDULE_ID |
| DOO_LINES_ALL_N1 | NORMAL | NONUNIQUE | SOURCE_LINE_ID |
| DOO_LINES_ALL_N1 | NORMAL | NONUNIQUE | SOURCE_ORDER_ID |
| DOO_LINES_ALL_N1 | NORMAL | NONUNIQUE | SOURCE_ORDER_SYSTEM |
| DOO_LINES_ALL_PK | NORMAL | UNIQUE | LINE_ID |
Constraints
| CONSTRAINT NAME | CONSTRAINT TYPE | SEARCH_CONDITION_VC |
| SYS_C00103065 | C | “LAST_UPDATED_BY” IS NOT NULL |
| SYS_C00103079 | C | “OPEN_FLAG” IS NOT NULL |
| SYS_C00103072 | C | “SOURCE_SCHEDULE_NUMBER” IS NOT NULL |
| SYS_C00103081 | C | “INVENTORY_ORGANIZATION_ID” IS NOT NULL |
| DOO_LINES_ALL_PK | P | |
| SYS_C00103062 | C | “CREATION_DATE” IS NOT NULL |
| SYS_C00103059 | C | “LINE_ID” IS NOT NULL |
| SYS_C00103064 | C | “LAST_UPDATE_DATE” IS NOT NULL |
| SYS_C00103066 | C | “SOURCE_LINE_ID” IS NOT NULL |
| SYS_C00103082 | C | “CATEGORY_CODE” IS NOT NULL |
| SYS_C00103060 | C | “LINE_NUMBER” IS NOT NULL |
| SYS_C00103068 | C | “SOURCE_ORDER_ID” IS NOT NULL |
| SYS_C00103080 | C | “INVENTORY_ITEM_ID” IS NOT NULL |
| SYS_C00103058 | C | “HEADER_ID” IS NOT NULL |
| SYS_C00103075 | C | “ORDERED_QTY” IS NOT NULL |
| SYS_C00103070 | C | “SOURCE_ORDER_SYSTEM” IS NOT NULL |
| SYS_C00103076 | C | “ORDERED_UOM” IS NOT NULL |
| SYS_C00103067 | C | “SOURCE_LINE_NUMBER” IS NOT NULL |
| SYS_C00103069 | C | “SOURCE_ORDER_NUMBER” IS NOT NULL |
| SYS_C00103073 | C | “SOURCE_REVISION_NUMBER” IS NOT NULL |
| SYS_C00103074 | C | “ITEM_TYPE_CODE” IS NOT NULL |
| SYS_C00103061 | C | “STATUS_CODE” IS NOT NULL |
| SYS_C00103063 | C | “CREATED_BY” IS NOT NULL |
| SYS_C00103077 | C | “ORG_ID” IS NOT NULL |
| SYS_C00103071 | C | “SOURCE_SCHEDULE_ID” IS NOT NULL |
| SYS_C00103078 | C | “OBJECT_VERSION_NUMBER” IS NOT NULL |
Related Posts:
Oracle Fusion Sales Order Table – DOO_HEADERS_ALL
DOO_FULFILL_LINES_ALL: Table Structure, Indexes, Constraints & Salient Points
Sales Order Table Linkage in Oracle Fusion – DOO_HEADERS_ALL
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