Oracle ERP standard order
Calculation On hand qty
(ON HAND) = (ATR) + (SOFT RESERV) + (pending transaction for the item)
Calculation On hand qty
(ON HAND) = (ATR) + (SOFT RESERV) + (pending transaction for the item)
Using this formula ATR is reverse calculated in the form.
Try running the below SQL to see soft and hard reservations.
(SELECT MSI.SEGMENT1,
HARD.STAGED_FLAG,
SUM(HARD.RESERVATION_QUANTITY)RESV_QTY
FROM APPS.MTL_SYSTEM_ITEMS_B MSI,
APPS.MTL_RESERVATIONS HARD
WHERE MSI.ORGANIZATION_ID = :ORG
AND HARD.STAGED_FLAG = 'Y' --HARD RESERVATION
AND MSI.INVENTORY_ITEM_ID = HARD.INVENTORY_ITEM_ID
AND MSI.SEGMENT1 = :ITEM
GROUP BY MSI.SEGMENT1,HARD.STAGED_FLAG)
(SELECT MSI.SEGMENT1,
SOFT.STAGED_FLAG,
SUM(SOFT.RESERVATION_QUANTITY) RESV_QTY
FROM APPS.MTL_RESERVATIONS SOFT,
APPS.MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.ORGANIZATION_ID = :ORG
AND SOFT.STAGED_FLAG IS NULL ---SOFT RESERVATION
AND MSI.INVENTORY_ITEM_ID = SOFT.INVENTORY_ITEM_ID
AND MSI.SEGMENT1 = :ITEM
GROUP BY MSI.SEGMENT1,SOFT.STAGED_FLAG);
Order Management Tables.
Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
List of Important Table
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.
Payment Terms
ra_terms Payment terms
AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details
Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits
Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text
Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all
Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all
Hold Relaese
oe_hold_releases_all Hold released Sales Order.
Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.
Cancel Orders
oe_order_lines_all Cancel Order Details.
API
create or replace function
gobo_get_available(in_item_id in number,in_from_org_id in number) return number as
--傳入item_id與org_id,返回可用庫存
avai_qty number;
begin
select nvl(sum(nvl(moq.transaction_quantity,0)),0)
-nvl(sum(nvl(mr.reservation_quantity,0)),0) into avai_qty
from mtl_onhand_quantities moq,
mtl_reservations mr,
mtl_item_revisions mir,
mtl_secondary_inventories ms
where moq.inventory_item_id=nvl(in_item_id,-1)
and moq.organization_id=nvl(in_from_org_id,-1)
and mr.staged_flag is null
and mr.organization_id=moq.organization_id
and mr.inventory_item_id=moq.inventory_item_id
and ms.organization_id(+)=moq.organization_id
and ms.secondary_inventory_name(+)=moq.subinventory_code
and ms.reservable_type(+) = 1
and nvl(mr.revision,'-1')=mir.revision(+)
and mir.inventory_item_id(+)=mr.inventory_item_id
and mir.organization_id(+)=mr.organization_id
and sysdate <= nvl(mir.ecn_initiation_date(+),sysdate)
and sysdate >= nvl(mir.implementation_date(+),sysdate);
if avai_qty is null then
avai_qty:=0;
end if;
return avai_qty;
end;
/---------------------------------
SELECT SUM(moq.transaction_quantity)
INTO p_onhand_qty
FROM mtl_onhand_quantities moq
WHERE moq.organization_id = p_inv_org_id
AND moq.subinventory_code = p_subinv
AND nvl(moq.locator_id, -1) = nvl(p_locator_id, -1)
AND moq.inventory_item_id = p_item_id
AND moq.lot_number = p_lot_number;
SELECT SUM(mr.primary_reservation_quantity)
INTO p_res_qty
FROM mtl_reservations mr
WHERE mr.organization_id = p_inv_org_id
AND mr.subinventory_code = p_subinv
AND mr.supply_source_type_id = 13
AND nvl(mr.locator_id, -1) = nvl(p_locator_id, -1)
AND mr.inventory_item_id = p_item_id
AND mr.lot_number = p_lot_number;
RETURN nvl(p_onhand_qty, 0) - nvl(p_res_qty, 0);
