 |
| 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
orders:
􀂃 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
returns, and return lines. Use the following tabs to locate your
orders:
􀂃 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:
• Item
• 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
1) OE_ORDER_HEADERS_ALL
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.
will change to ‘BOOKED’ , BOOKED_DATE,
At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts into MTL_DEMAND.
begin
mo_global.set_POLICY_conteXt('S','204');
END;
when you are writing queries set org context then write as I mentioned above
SELECT hzp.party_name,
hzp.party_number,
hzl.address1,
hzl.address2,
hzl.address3,
hzl.city,
hzl.postal_code
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
hzp.party_number,
hzl.address1,
hzl.address2,
hzl.address3,
hzl.city,
hzl.postal_code
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
s
SELECT PARTY_ID
FROM hz_parties
WHERE party_name LIKE 'A. C. Networks'
SELECT ACCOUNT_NUMBER
FROM HZ_CUST_ACCOUNTS
WHERE PARTY_ID = 1290
SELECT salesrep_id, price_list_id,flow_status_code
FROM OE_ORDER_HEADERS_ALL
WHERE order_number = '67507'
SELECT *
FROM JTF_RS_SALESREPS
WHERE SALESREP_ID = 1006
SELECT name
FROM QP_PRICE_LISTS_V
WHERE price_list_id = 1000
2)OE_ORDER_LINES_ALL
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
FROM QP_PRICE_LISTS_V
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
SELECT *
FROM mtl_system_items_b
WHERE segment1 = 'AS10000' AND ORGANIZATION_ID = 204
validation for ORDER_QUANTITY_UOM
SELECT UOM_CODE
FROM MTL_UOM_CONVERSIONS
WHERE unit_of_measure = 'EACH'
when the record is booked data will be populated in the "WSH_DELIVERY_DETAILS'
table
Here
oe_orders_header_all(header_id)=wsh_delivery_details(header_id)
if the items are not available in the inventory the order become "BACKORDERED"
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)
SELECT *
FROM wsh_delivery_assignments
WHERE delivery_detail_id = 4577468
SELECT *
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 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
1. MTL_TXN_REQUEST_HEADERS
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.
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.
2. MTL_TXN_REQUEST_LINES
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
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.
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.
to the same location.
SELECT *
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
Mtl_material_transactions mmt
WHERE ooha.HEADER_ID = ooha.HEADER_ID
AND oola.LINE_ID = mmt.TRX_SOURCE_LINE_ID
Happy Reading....
