Sales Order Line Status Flow and Meaning

Below are some of the different statuses of Sales Order Line with brief explanation

OM = Order Management Sales order form
SE = Shipping Transactions or execution form

1) Entered (OM): Order is saved but not booked

2) Booked (OM): Order is Booked.

3) Awaiting Shipping (OM): Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:

4) Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used

5) Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.

6) Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.

7) Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances.

• The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.

• At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.

• The user manually Backorders the entire delivery.
  

8) Shipped (SE): The delivery line is shipped confirmed.

9) Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open.

10) Picked (OM): Pick release is complete, both allocations and pick confirm

11) Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred

12) Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete.

13) Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled

14) Fulfilled (OM): All lines in a fulfillment set are fulfilled.

15) Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created.

16) Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

17) Closed (OM): Closed indicates that the line is closed.

18) Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

Reference: Oracle Metalink

SQL Loader limit number of rows

Based on a query from one of our reader here is an example to illustrate how to limit number of rows to be loaded in SQL Loader. This can be done using option LOAD

OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
    item_number     "trim(:item_number)"
  , vendor_name "trim(:vendor_name)"
  , vendor_site_name "trim(:vendor_site_name)"
  , supplier_item   "trim(:supplier_item)"
  , process_flag  Constant 'UNPROCESSED'
)

In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows

sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5  skip=1

Keywords: SQL*LOADER, ERRORS, SKIP, LOAD

Query to Link between Order Management and Account Receivables

On request I continue to write further on Post Relation between AR Invoice and Sales Order tables and based on the standard assumption, provide a query that can be used to link Invoice and Sales Order.

Query below can be handy

SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND    order_number = :p_order_number

Keywords: Sales Order, AR, OM