DOO_FULFILL_LINES_ALL: the table which holds the fulfilment details of Sales Order Line such as Ordered Quantity, Shipped Quantity, Canceled ( Cancelled) Quantity etc. This table is one of the key tables in Fusion O2C Cycle. Few important points to remember:
1. When cancellation is done, Ordered Quantity is reduced and cancelled quantity is increased. So for a line to be cancelled, Ordered Quantity will become 0 in doo_fulfill_lines_all table.
2. You can connect this table to doo_headers_all using header_id and doo_lines_all table using line_id.
3. There are 2 prices in this table. Unit List Price and Unit Selling Price. Unit selling price is most probably you’ll be using.
4. DOO_FULFiLL_LiNES_ALL table is the child table of DOO_LINES_ALL table which is used to store the order line level information of Sales Order.
5. Want to get the details of the sales order, this post is meant for you.
Now let’s cut to the chase and see the table definition, constraints and indexes.
DOO_FULFILL_LINES_ALL Column Details table
| COLUMN NAME | DATA TYPE | DATA LENGTH | NULLABLE |
| FULFILL_LINE_ID | NUMBER | 22 | N |
| SERVICE_CANCEL_DATE | DATE | 7 | Y |
| RECEIVABLES_ORG_ID | NUMBER | 22 | Y |
| OBJECT_VERSION_NUMBER | NUMBER | 22 | N |
| AGREEMENT_HEADER_ID | NUMBER | 22 | Y |
| HEADER_ID | NUMBER | 22 | N |
| AGREEMENT_LINE_ID | NUMBER | 22 | Y |
| LINE_ID | NUMBER | 22 | N |
| AGREEMENT_VERSION_NUMBER | NUMBER | 22 | Y |
| FULFILL_LINE_NUMBER | NUMBER | 22 | N |
| INVENTORY_ITEM_ID | NUMBER | 22 | N |
| ITEM_TYPE_CODE | VARCHAR2 | 120 | N |
| ORDERED_QTY | NUMBER | 22 | N |
| STATUS_CODE | VARCHAR2 | 120 | N |
| 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 | 22 | N |
| COMP_SEQ_PATH | VARCHAR2 | 4000 | Y |
| OWNER_ID | NUMBER | 22 | Y |
| CREATION_DATE | TIMESTAMP(6) | 11 | N |
| CREATED_BY | VARCHAR2 | 256 | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | 11 | N |
| LAST_UPDATED_BY | VARCHAR2 | 256 | N |
| LAST_UPDATE_LOGIN | VARCHAR2 | 128 | Y |
| FULFILL_ORG_ID | NUMBER | 22 | Y |
| CANCELED_QTY | NUMBER | 22 | Y |
| ORDERED_UOM | VARCHAR2 | 12 | N |
| PURCHASING_UOM | VARCHAR2 | 12 | Y |
| SHIPPED_UOM | VARCHAR2 | 12 | Y |
| RMA_DELIVERED_QTY | NUMBER | 22 | Y |
| FULFILLED_QTY | NUMBER | 22 | Y |
| SHIPPED_QTY | NUMBER | 22 | Y |
| RESERVED_QTY | NUMBER | 22 | Y |
| RESERVABLE_FLAG | VARCHAR2 | 4 | Y |
| SHIPPABLE_FLAG | VARCHAR2 | 4 | Y |
| INVOICEABLE_ITEM_FLAG | VARCHAR2 | 4 | Y |
| INVOICE_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| RETURNABLE_FLAG | VARCHAR2 | 4 | Y |
| INVOICE_INTERFACED_FLAG | VARCHAR2 | 4 | Y |
| BILL_TO_CONTACT_ID | NUMBER | 22 | Y |
| BILL_TO_CUSTOMER_ID | NUMBER | 22 | Y |
| BILL_TO_SITE_USE_ID | NUMBER | 22 | Y |
| SOURCE_ORG_ID | NUMBER | 22 | Y |
| ORG_ID | NUMBER | 22 | N |
| REQUEST_ARRIVAL_DATE | DATE | 7 | Y |
| REQUEST_SHIP_DATE | DATE | 7 | Y |
| SCHEDULE_ARRIVAL_DATE | DATE | 7 | Y |
| SCHEDULE_SHIP_DATE | DATE | 7 | Y |
| PROMISE_ARRIVAL_DATE | DATE | 7 | Y |
| PROMISE_SHIP_DATE | DATE | 7 | Y |
| ACTUAL_SHIP_DATE | DATE | 7 | Y |
| FULFILLMENT_DATE | DATE | 7 | Y |
| CUSTOMER_ITEM_ID | NUMBER | 22 | Y |
| CUSTOMER_PO_LINE_NUMBER | VARCHAR2 | 200 | Y |
| CUSTOMER_PO_NUMBER | VARCHAR2 | 200 | Y |
| DEMAND_CLASS_CODE | VARCHAR2 | 120 | Y |
| EXTENDED_AMOUNT | NUMBER | 22 | Y |
| FOB_POINT_CODE | VARCHAR2 | 120 | Y |
| CARRIER_ID | NUMBER | 22 | Y |
| INVOICING_RULE_ID | NUMBER | 22 | Y |
| EXEMPTION_CERTIFICATE_NUMBER | VARCHAR2 | 320 | Y |
| ACCOUNTING_RULE_ID | NUMBER | 22 | Y |
| LINE_TYPE_CODE | VARCHAR2 | 120 | Y |
| CATEGORY_CODE | VARCHAR2 | 120 | N |
| OPEN_FLAG | VARCHAR2 | 4 | N |
| CANCELED_FLAG | VARCHAR2 | 4 | Y |
| OVERRIDE_SCHEDULE_DATE_FLAG | VARCHAR2 | 4 | Y |
| ORIGINAL_INVENTORY_ITEM_ID | NUMBER | 22 | Y |
| GOP_REFERENCE_ID | VARCHAR2 | 1200 | Y |
| PACKING_INSTRUCTIONS | VARCHAR2 | 4000 | Y |
| PARENT_FULFILL_LINE_ID | NUMBER | 22 | Y |
| PAYMENT_TERM_ID | NUMBER | 22 | Y |
| REQUEST_TYPE | VARCHAR2 | 120 | Y |
| RETURN_REASON_CODE | VARCHAR2 | 120 | Y |
| ORIG_SYS_DOCUMENT_REF | VARCHAR2 | 200 | Y |
| ORIG_SYS_DOCUMENT_LINE_REF | VARCHAR2 | 200 | Y |
| ROOT_PARENT_FULFILL_LINE_ID | NUMBER | 22 | Y |
| SHIP_CLASS_OF_SERVICE | VARCHAR2 | 120 | Y |
| SHIP_MODE_OF_TRANSPORT | VARCHAR2 | 120 | Y |
| SHIP_TO_CONTACT_ID | NUMBER | 22 | Y |
| SHIP_TO_CUSTOMER_ID | NUMBER | 22 | Y |
| SHIP_TO_SITE_USE_ID | NUMBER | 22 | Y |
| FULFILL_TOLERANCE_ABOVE | NUMBER | 22 | Y |
| FULFILL_TOLERANCE_BELOW | NUMBER | 22 | Y |
| SHIPMENT_PRIORITY_CODE | VARCHAR2 | 120 | Y |
| SHIPPING_INSTRUCTIONS | VARCHAR2 | 4000 | Y |
| SHIP_SET_NAME | VARCHAR2 | 200 | Y |
| SPLIT_FROM_FLINE_ID | NUMBER | 22 | Y |
| PARTIAL_SHIP_ALLOWED_FLAG | VARCHAR2 | 4 | Y |
| SUBSTITUTE_ALLOWED_FLAG | VARCHAR2 | 4 | Y |
| SUBSTITUTE_REASON_CODE | VARCHAR2 | 120 | Y |
| TAX_EXEMPT_FLAG | VARCHAR2 | 4 | Y |
| TAX_CLASSIFICATION_CODE | VARCHAR2 | 200 | Y |
| DELTA_TYPE | NUMBER | 22 | Y |
| TAX_EXEMPTION_REASON_CODE | VARCHAR2 | 120 | Y |
| UNIT_LIST_PRICE | NUMBER | 22 | Y |
| UNIT_SELLING_PRICE | NUMBER | 22 | Y |
| REFERENCE_FLINE_ID | NUMBER | 22 | Y |
| LATEST_ACCEPTABLE_SHIP_DATE | DATE | 7 | Y |
| LATEST_ACCEPTABLE_ARRIVAL_DATE | DATE | 7 | Y |
| EARLIEST_ACCEPTABLE_SHIP_DATE | DATE | 7 | Y |
| ON_HOLD | VARCHAR2 | 4 | Y |
| RESERVATION_ID | NUMBER | 22 | Y |
| ACTUAL_COMPLETION_DATE | DATE | 7 | Y |
| INVENTORY_ORGANIZATION_ID | NUMBER | 22 | N |
| FULFILL_INSTANCE_ID | NUMBER | 22 | Y |
| STATUS_RULESET_ID | NUMBER | 22 | Y |
| REMNANT_FLAG | VARCHAR2 | 4 | Y |
| FULFILLMENT_SPLIT_REF_ID | NUMBER | 22 | Y |
| ESTIMATE_MARGIN | NUMBER | 22 | Y |
| ESTIMATE_FULFILLMENT_COST | NUMBER | 22 | Y |
| FREIGHT_TERMS_CODE | VARCHAR2 | 120 | Y |
| CANCEL_REASON_CODE | VARCHAR2 | 120 | Y |
| LATEST_EXT_FULFILL_LINE_NUMBER | NUMBER | 22 | Y |
| GOP_REQUEST_REGION | VARCHAR2 | 1020 | Y |
| SCHEDULING_REASON_CODE | VARCHAR2 | 120 | Y |
| SUBINVENTORY | VARCHAR2 | 40 | Y |
| SUPPLIER_ID | NUMBER | 22 | Y |
| SUPPLIER_SITE_ID | NUMBER | 22 | Y |
| SHIP_TO_PARTY_ID | NUMBER | 22 | Y |
| SHIP_TO_PARTY_SITE_ID | NUMBER | 22 | Y |
| SHIP_TO_PARTY_CONTACT_ID | NUMBER | 22 | Y |
| CONFIG_ITEM_REFERENCE | VARCHAR2 | 4000 | Y |
| ITEM_SUB_TYPE_CODE | VARCHAR2 | 120 | Y |
| REQUISITION_INVENTORY_ORG_ID | NUMBER | 22 | Y |
| REQUISITION_BU_ID | NUMBER | 22 | Y |
| FULFILLMENT_MODE | VARCHAR2 | 120 | Y |
| PO_STATUS_CODE | VARCHAR2 | 120 | Y |
| BUYER_ID | NUMBER | 22 | Y |
| REQUEST_CANCEL_DATE | TIMESTAMP(6) | 11 | Y |
| COMMENTS | VARCHAR2 | 4000 | Y |
| DEFAULT_TAXATION_COUNTRY | VARCHAR2 | 8 | Y |
| FIRST_PTY_REG_ID | NUMBER | 22 | Y |
| THIRD_PTY_REG_ID | NUMBER | 22 | Y |
| DOCUMENT_SUB_TYPE | VARCHAR2 | 960 | Y |
| TAX_INVOICE_NUMBER | VARCHAR2 | 600 | Y |
| TAX_INVOICE_DATE | DATE | 7 | Y |
| FINAL_DISCHARGE_LOCATION_ID | NUMBER | 22 | Y |
| PROD_FC_CATEG_ID | NUMBER | 22 | Y |
| PRODUCT_TYPE | VARCHAR2 | 960 | Y |
| PRODUCT_CATEGORY | VARCHAR2 | 960 | Y |
| TRX_BUSINESS_CATEGORY | VARCHAR2 | 960 | Y |
| ASSESSABLE_VALUE | NUMBER | 22 | Y |
| USER_DEFINED_FISC_CLASS | VARCHAR2 | 120 | Y |
| INTENDED_USE_CLASSIF_ID | NUMBER | 22 | Y |
| DESTINATION_LOCATION_ID | NUMBER | 22 | Y |
| DESTINATION_ORG_ID | NUMBER | 22 | Y |
| QUANTITY_PER_MODEL | NUMBER | 22 | Y |
| CONFIG_INVENTORY_ITEM_ID | NUMBER | 22 | Y |
| PURCHASING_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| CUSTOMER_PO_SCHEDULE_NUMBER | VARCHAR2 | 200 | Y |
| SUPPLY_STATUS_CODE | VARCHAR2 | 120 | Y |
| SHIP_TO_CONTACT_POINT_ID | NUMBER | 22 | Y |
| PREF_OVERRIDDEN_BITSET | NUMBER | 22 | Y |
| APPLIED_PRICE_LIST_ID | NUMBER | 22 | Y |
| PRICED_ON | TIMESTAMP(6) | 11 | Y |
| REQUIRED_FULFILLMENT_DATE | TIMESTAMP(6) | 11 | Y |
| COMPONENT_ID_PATH | VARCHAR2 | 4000 | Y |
| VALID_CONFIGURATION_FLAG | VARCHAR2 | 4 | Y |
| CONFIGURATOR_PATH | VARCHAR2 | 4000 | Y |
| CONFIG_HEADER_ID | NUMBER | 22 | Y |
| CONFIG_REVISION_NUMBER | NUMBER | 22 | Y |
| SHOW_IN_SALES | NUMBER | 22 | Y |
| CHANGE_ELIGIBLE_FLAG | VARCHAR2 | 4 | Y |
| MODIFIED_FLAG | VARCHAR2 | 4 | Y |
| BILL_TO_CONTACT_POINT_ID | NUMBER | 22 | Y |
| UNIT_QUANTITY | NUMBER | 22 | Y |
| CONFIG_CREATION_DATE | TIMESTAMP(6) | 11 | Y |
| CONTRACT_START_DATE | DATE | 7 | Y |
| CONTRACT_END_DATE | DATE | 7 | Y |
| UNREFERENCED_RETURN_FLAG | VARCHAR2 | 4 | Y |
| TOTAL_CONTRACT_QUANTITY | NUMBER | 22 | Y |
| TOTAL_CONTRACT_AMOUNT | NUMBER | 22 | Y |
| PROCESS_INSTANCE_ID | NUMBER | 22 | Y |
| PROCESS_NUMBER | VARCHAR2 | 200 | Y |
| CREDIT_CHK_AUTH_NUM | VARCHAR2 | 960 | Y |
| CREDIT_CHK_AUTH_EXP_DATE | TIMESTAMP(6) | 11 | Y |
| TRADE_COMPLIANCE_RESULT_CODE | VARCHAR2 | 120 | Y |
| TRADE_COMPLIANCE_DATE | TIMESTAMP(6) | 11 | Y |
| TRANSPORTATION_PLANNED_FLAG | VARCHAR2 | 4 | Y |
| CREATED_IN_RELEASE | VARCHAR2 | 60 | Y |
| CONFIG_TRADE_COMPL_RESULT_CODE | VARCHAR2 | 120 | Y |
| BILLING_TRX_TYPE_ID | NUMBER | 22 | Y |
| SERVICE_DURATION | NUMBER | 22 | Y |
| SERVICE_DURATION_PERIOD_CODE | VARCHAR2 | 40 | Y |
| SALES_PRODUCT_TYPE_CODE | VARCHAR2 | 120 | Y |
| ACTION_TYPE_CODE | VARCHAR2 | 120 | Y |
| ASSET_TRACKED_FLAG | VARCHAR2 | 4 | Y |
| ASSET_GROUP_NUMBER | VARCHAR2 | 120 | Y |
| PROCESS_SET | VARCHAR2 | 1020 | Y |
| SALESPERSON_ID | NUMBER | 22 | Y |
| BATCH_ID | NUMBER | 22 | Y |
| PROCESS_ID | NUMBER | 22 | Y |
| PRJ_REC_INDICATOR | VARCHAR2 | 4 | Y |
| CREATION_MODE | VARCHAR2 | 120 | Y |
| MDO_FLAG | VARCHAR2 | 4 | Y |
| FULFILLMENT_GROUP_ID | NUMBER | 22 | Y |
| SELLING_PROFIT_CENTER_BU_ID | NUMBER | 22 | Y |
| USER_UPDATE_INDICATOR_BITSET | NUMBER | 22 | Y |
Index Details:
| INDEX NAME | INDEX TYPE | UNIQUE? | COLUMN NAME |
| DOO_FULFILL_LINES_ALL_FK1 | NORMAL | N | LINE_ID |
| DOO_FULFILL_LINES_ALL_FK10 | NORMAL | N | SHIP_TO_PARTY_ID |
| DOO_FULFILL_LINES_ALL_FK2 | NORMAL | N | HEADER_ID |
| DOO_FULFILL_LINES_ALL_FK4 | NORMAL | N | INVENTORY_ITEM_ID |
| DOO_FULFILL_LINES_ALL_FK4 | NORMAL | N | OPEN_FLAG |
| DOO_FULFILL_LINES_ALL_FK5 | NORMAL | N | PARENT_FULFILL_LINE_ID |
| DOO_FULFILL_LINES_ALL_FK6 | NORMAL | N | ROOT_PARENT_FULFILL_LINE_ID |
| DOO_FULFILL_LINES_ALL_FK7 | NORMAL | N | SHIP_TO_SITE_USE_ID |
| DOO_FULFILL_LINES_ALL_FK7 | NORMAL | N | SHIP_TO_CUSTOMER_ID |
| DOO_FULFILL_LINES_ALL_FK8 | NORMAL | N | SPLIT_FROM_FLINE_ID |
| DOO_FULFILL_LINES_ALL_FK9 | NORMAL | N | FULFILL_ORG_ID |
| DOO_FULFILL_LINES_ALL_FK9 | NORMAL | N | FULFILL_INSTANCE_ID |
| DOO_FULFILL_LINES_ALL_N1 | NORMAL | N | SOURCE_SCHEDULE_ID |
| DOO_FULFILL_LINES_ALL_N1 | NORMAL | N | SOURCE_LINE_ID |
| DOO_FULFILL_LINES_ALL_N1 | NORMAL | N | SOURCE_ORDER_ID |
| DOO_FULFILL_LINES_ALL_N1 | NORMAL | N | SOURCE_ORDER_SYSTEM |
| DOO_FULFILL_LINES_ALL_N2 | NORMAL | N | OPEN_FLAG |
| DOO_FULFILL_LINES_ALL_N2 | NORMAL | N | STATUS_CODE |
| DOO_FULFILL_LINES_ALL_N3 | NORMAL | N | SCHEDULE_SHIP_DATE |
| DOO_FULFILL_LINES_ALL_N4 | NORMAL | N | FULFILLMENT_SPLIT_REF_ID |
| DOO_FULFILL_LINES_ALL_N5 | NORMAL | N | LAST_UPDATE_DATE |
| DOO_FULFILL_LINES_ALL_N6 | NORMAL | N | AGREEMENT_HEADER_ID |
| DOO_FULFILL_LINES_ALL_N6 | NORMAL | N | AGREEMENT_LINE_ID |
| DOO_FULFILL_LINES_ALL_N7 | NORMAL | N | BATCH_ID |
| DOO_FULFILL_LINES_ALL_N7 | NORMAL | N | PROCESS_ID |
| DOO_FULFILL_LINES_ALL_N8 | NORMAL | N | PROCESS_INSTANCE_ID |
| DOO_FULFILL_LINES_ALL_PK | NORMAL | Y | FULFILL_LINE_ID |
Related Posts
Sales Order Table
Sales Order Query
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
From: Mohit Saxena
Author of “Oracle Fusion: Unconventional Ways to Enhance the Performance of BI Reports“
1 thought on “DOO_FULFILL_LINES_ALL: Columns, Indexes & Imp Points”
Comments are closed.