Total Pageviews

February 15, 2015

2/15/2015 09:46:00 AM

Functional Technical Prospective
   Overview to Order Management

 What Is Oracle Order Management?

          Part of an integrated suite of e-business applications for the enterprise
          Well-organized, extremely comprehensive product
          Multifaceted solution
          Highly extensible and customization to meet your business requirements
          Well-designed architecture
          Easy user interface
          Oracle Order Management (including basic pricing)
          Oracle Shipping Execution
          Other applications that support the Order
          Management Suite:
          Oracle Configurator
          Oracle Advanced Pricing
          Oracle Release Management
          e-Commerce Gateway
          Customer Relationship Management Suite
          Customer, Carrier, Shipper, and Order Management Portals
          Enables management of customer and customer relationship information including defining ship, arrival, and fulfillment sets
          Automatically checks credit during order entry
          Uses Workflow for required approvals and managing order changes
          Uses Processing Constraints set by responsibility to control changes to orders
Supplies product configuration capabilities for:
          Assemble-to-order (ATO)
          Pick-to-order (PTO)
          Defines items using the Oracle Inventory scope of functions and defines bills of material using the Oracle Bills of Material functions
          Offers item cross-referencing usage as an entry option
          Allows decimal quantity handling
          Enables available-to-promise (ATP) viewing and reserving across the supply chain
          Communicates demand to manufacturing

Entering Header Information

Use the Find Orders form to query existing orders, order lines,
returns, and return lines. Use the following tabs to locate your
􀂃 Order Information tab: Matches criteria against header level values
when finding orders.
􀂃 Line Information tab: Matches criteria against line level values when finding orders.
􀂃 Advanced tab: Allows you to specify advanced controls, such as whether to find closed orders or cancelled orders/lines.
􀂃 Holds Information tab: Allows you to specify holds-related criteria. It also finds hold sources required to be released

In the Sales Orders window, Line Items: Main tab,
enter the:
          Quantity or decimal quantity
          Unit of measure
          Tax code
Entering Line Pricing
In the Sales Orders window, Line Items:
Pricing tab, enter the:
       Unit selling price
       Extended price
       Price list name
       Tax amount
       Payment terms
       Agreement information
Modify price (optional)
Adjusting Line Prices
1.  Select Action and choose View Adjustments.
2.  The Adjustments window opens; view modifiers selected by the pricing engine.
3.  If you want to override an overridable modifier, place your cursor on it and click Apply.

Order Entry methods

Order Header

Header_id, order_type_id, booked_flag, order_number,version_number, open_flag, order_category_code, ship_to_org_id,ship_from_org_id, sold_to_org_id, sold_from_org_id
stores header information for orders in Order Management.

ORDER_TYPE_ID                     Order Transaction Type Identifier
ORDER_NUMBER                     User-visible number of the order
CUST_PO_NUMBER                 Customer Purchase Order Number(po_headers_all.segment1)
CANCELLED_FLAG                  Cancelled Flag
OPEN_FLAG                             Indicates whether the order has been closed ('N') or not ('Y')
BOOKED_FLAG                        Indicates whether order is booked ('Y') or not ('N')
FLOW_STATUS_CODE Workflow status for the header

 after booking BOOKED_FLAG will change to ‘Y’ ,FLOW_STATUS_CODE
 will change to ‘BOOKED’ , BOOKED_DATE,
At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts into MTL_DEMAND.

Technical Points to Note

when you are writing queries set org context then write as I mentioned above

SELECT   hzp.party_name,
  FROM   hz_cust_accounts hca,
         hz_cust_acct_sites hcas,
         hz_cust_site_uses hcsu,
         hz_locations hzl,
         hz_parties hzp,
         hz_party_sites hzps,
         oe_order_headers ooah
 WHERE   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
         AND hcas.cust_account_id = hca.cust_account_id
         AND hca.party_id = hzp.party_id
         AND hcas.party_site_id = hzps.party_site_id
         AND hzps.location_id = hzl.location_id
         and ooah.sold_to_org_id=hzp.party_id
         and ooah.sold_to_org_id=hca.party_id 
         AND OOAH.ORDER_NUMBER='67504'
         AND hcsu.SITE_use_code='BILL_TO'
         AND HCSU.primary_flag='Y'  
IF you want to get SHIP_TO address you have to USE hcsu.SITE_use_code='SHIP_TO'
It is good practice to have move these programs in utility program .
I will explain what is the utility package in tomorrow' update

In oe_order_headers_all the sold_to_org_id  is party_id but while entering the orders it is ACCOUNT_NUMBER


  FROM   hz_parties
 WHERE   party_name LIKE 'A. C. Networks'
 WHERE   PARTY_ID = 1290

SELECT   salesrep_id, price_list_id,flow_status_code
 WHERE   order_number = '67507'


SELECT   name
 WHERE   price_list_id = 1000

line_id,header_id, line_type_id, line_number,unit_selling_price                  

stores information for all order lines in Order Management

ORDERED_QUANTITY              Quantity Ordered
FULFILLED_QUANTITY             Fulfilled Quantity
SHIPPING_QUANTITY               Shipping Quantity
INVOICE_INTERFACE_STATUS_CODE  To indicate whether the invoicing activity is complete
SHIPPABLE_FLAG                    A flag to indicate whether a line is  shippable not
Line Item tab Information

SELECT   ordered_item, ORDER_QUANTITY_UOM, price_list_id
  FROM   oe_order_lines_all
 WHERE   header_id = 209132

SELECT   name
 WHERE   price_list_id = 1000

 select ordered_item,ORDER_QUANTITY_UOM
  from oe_order_lines_all
 where header_id=209132

Validation for Ordered_item(this is segment1 in mtl_system_items_b) table
  FROM   mtl_system_items_b
 WHERE   segment1 = 'AS10000' AND ORGANIZATION_ID = 204

validation for ORDER_QUANTITY_UOM

 WHERE   unit_of_measure = 'EACH'

when the record is booked data will be populated in the "WSH_DELIVERY_DETAILS'


if the items are not available in the inventory the order become "BACKORDERED"

I took another item which has  available on_hand_quantity
you can check on_hand_quantity
mtl_onhand_quantities table

Once the pick release has been done data will be reflected like this

Tables affected (wsh_delivery_details)

  FROM   wsh_delivery_assignments
 WHERE   delivery_detail_id = 4577468

  FROM   wsh_new_deliveries
 WHERE   delivery_id = 4564373

When  item  is picked data will be inserted into  these tables wsh_delivery_assignments, wsh_new_deliveries,mtl_material_transactions

if auto_pick confirm no

If Auto Pick Confirm is set to No, then we have to transact the Move Order by navigating to Inventory Responsibility. Move Order number is the Batch Number shown in the Release Sales Orders Screen.

Navigation>> Inventory Super User>> Move Orders>> Transact Move orders
This table stores all of the move order headers. The headers contain all information which pertains to entire move orders, including the transaction type of the move order, the move order type, the move order status, and the request number of the move order.
The table MTL_TXN_REQUEST_LINES stores all of the move order lines. The lines are requests to move some quantity of an item from a source location to a destination location or account. Each move order line must be tied to a specific move order 
header.When a line is detailed or pick released, the quantity detailed is updated appropriately and transaction lines are created in 

MTL_MATERIAL_TRANSACTIONS_TEMP. When the transaction lines are transacted, the quantity delivered is updated.

but in mtl_material_transactions for every pick release of one item ,data will be populated as two records,will explain why it will happen in tomorrow's blog

Actual Arrival Date - The date the order line arrives at the customer site.

Actual Ship Date - The date the order line is shipped. This date is recorded by the

ship confirm action.

Ship Set - A set of lines which will be shipped together from the same warehouse
to the same location.

  FROM   oe_order_headers_all ooha,
         oe_order_lines_all oola,
         Mtl_material_transactions mmt
         AND oola.LINE_ID = mmt.TRX_SOURCE_LINE_ID

Happy Reading....