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.
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...
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
Check out the Amazon Deals of the day based on your interests..
1 thought on “DOO_FULFILL_LINES_ALL: Columns, Indexes & Imp Points”
Comments are closed.