OM Tables
Order Management Tables.
--Entered
SELECT *
FROM oe_order_headers_all
WHERE 1 = 1;
--> record created in header table
SELECT *
FROM oe_order_lines_all
WHERE 1 = 1;
--> Lines for particular records
SELECT *
FROM oe_price_adjustments
WHERE 1 = 1;
--> When discount gets applied
SELECT *
FROM oe_order_price_attribs
WHERE 1 = 1;
--> If line has price attributes then populated
SELECT *
FROM oe_order_holds_all
WHERE 1 = 1;
--> If any hold applied for order like credit check etc. Booked
SELECT *
FROM oe_order_headers_all
WHERE 1 = 1;
--> Booked_flag=Y Order booked.
SELECT *
FROM wsh_delivery_details
WHERE 1 = 1;
--> Released_status Ready to release Pick Released
SELECT *
FROM wsh_delivery_details
WHERE 1 = 1;
--> Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
SELECT *
FROM wsh_picking_batches
WHERE 1 = 1;
--> After batch is created for pick release.
SELECT *
FROM mtl_reservations
WHERE 1 = 1;
--> This is only soft reservations. No physical movement of stock Full Transaction
SELECT *
FROM mtl_material_transactions
WHERE 1 = 1;
--> No of records in mtl_material_transactions
SELECT *
FROM mtl_txn_request_headers
WHERE 1 = 1;
-->
SELECT *
FROM mtl_txn_request_lines
WHERE 1 = 1;
-->
SELECT *
FROM wsh_delivery_details
WHERE 1 = 1;
--> Released to warehouse.
SELECT *
FROM wsh_new_deliveries
WHERE 1 = 1;
--> if Auto-Create is Yes then data populated.
SELECT *
FROM wsh_delivery_assignments
WHERE 1 = 1;
--> deliveries get assigned Pick Confirmed
SELECT *
FROM wsh_delivery_details
WHERE 1 = 1;
--> Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock Ship Confirmed
SELECT *
FROM wsh_delivery_details
WHERE 1 = 1;
--> Released_status=C Y To C:Shipped ;Delivery
--Note get printed Delivery assigned to trip stopquantity will be decreased from staged
SELECT *
FROM mtl_material_transactions
WHERE 1 = 1;
--> On the ship confirm form, check Ship all box
SELECT *
FROM wsh_new_deliveries
WHERE 1 = 1;
--> If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
SELECT *
FROM oe_order_lines_all
WHERE 1 = 1;
--> Shipped_quantity get populated.
SELECT *
FROM wsh_delivery_legs
WHERE 1 = 1;
--> 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
SELECT *
FROM oe_order_headers_all
WHERE 1 = 1;
--> If all the lines get shipped then only flag N Autoinvoice
SELECT *
FROM wsh_delivery_details
WHERE 1 = 1;
--> Released_status=I Need to run workflow background process.
SELECT *
FROM ra_interface_lines_all
WHERE 1 = 1;
--> Data will be populated after wkfw process.
SELECT *
FROM ra_customer_trx_all
WHERE 1 = 1;
--> After running Autoinvoice Master Program for
SELECT *
FROM ra_customer_trx_lines_all
WHERE 1 = 1;
--> specific batch transaction tables get
--populated Price Details
SELECT *
FROM qp_list_headers_b
WHERE 1 = 1;
--> To Get Item Price Details.
SELECT *
FROM qp_list_lines
WHERE 1 = 1;
-->
--Items On Hand Qty
SELECT *
FROM mtl_onhand_quantities
WHERE 1 = 1;
--> TO check On Hand Qty Items.
--Payment Terms
SELECT *
FROM ra_terms
WHERE 1 = 1;
--> Payment terms
--AutoMatic Numbering System
SELECT *
FROM ar_system_parametes_all
WHERE 1 = 1;
--> you can chk Automactic Numbering is enabled/disabled.
--Customer Information
SELECT *
FROM hz_parties
WHERE 1 = 1;
--> Get Customer information include name,contacts,Address and Phone
SELECT *
FROM hz_party_sites
WHERE 1 = 1;
-->
SELECT *
FROM hz_locations
WHERE 1 = 1;
-->
SELECT *
FROM hz_cust_accounts
WHERE 1 = 1;
-->
SELECT *
FROM hz_cust_account_sites_all
WHERE 1 = 1;
-->
SELECT *
FROM hz_cust_site_uses_all
WHERE 1 = 1;
-->
SELECT *
FROM ra_customers
WHERE 1 = 1;
-->
--Document Sequence
SELECT *
FROM fnd_document_sequences
WHERE 1 = 1;
--> Document Sequence Numbers
SELECT *
FROM fnd_doc_sequence_categories
WHERE 1 = 1;
-->
SELECT *
FROM fnd_doc_sequence_assignments
WHERE 1 = 1;
-->
--Default rules for Price List
SELECT *
FROM oe_def_attr_def_rules
WHERE 1 = 1;
--> Price List Default Rules
SELECT *
FROM oe_def_attr_condns
WHERE 1 = 1;
-->
SELECT *
FROM ak_object_attributes
WHERE 1 = 1;
-->
--End User Details
SELECT *
FROM csi_t_party_details
WHERE 1 = 1;
--> To capture End user Details
--Sales Credit Sales Credit Information(How much credit can get)
SELECT *
FROM oe_sales_credits
WHERE 1 = 1;
-->
--Attaching Documents
SELECT *
FROM fnd_attached_documents
WHERE 1 = 1;
---> Attched Documents and Text information
SELECT *
FROM fnd_documents_tl
WHERE 1 = 1;
-->
SELECT *
FROM fnd_documents_short_text
WHERE 1 = 1;
-->
--Blanket Sales Order
SELECT *
FROM oe_blanket_headers_all
WHERE 1 = 1;
--> Blanket Sales Order Information.
SELECT *
FROM oe_blanket_lines_all
WHERE 1 = 1;
-->
--Processing Constraints
SELECT *
FROM oe_pc_assignments
WHERE 1 = 1;
--> Sales order Shipment schedule Processing
--Constratins
SELECT *
FROM oe_pc_exclusions
WHERE 1 = 1;
-->
--Sales Order Holds
SELECT *
FROM oe_hold_definitions
WHERE 1 = 1;
--> Order Hold and Managing Details.
SELECT *
FROM oe_hold_authorizations
WHERE 1 = 1;
-->
SELECT *
FROM oe_hold_sources_all
WHERE 1 = 1;
-->
SELECT *
FROM oe_order_holds_all
WHERE 1 = 1;
-->
--Hold Relaese
SELECT *
FROM oe_hold_releases_all
WHERE 1 = 1;
--> Hold released Sales Order.
--Credit Chk Details
SELECT *
FROM oe_credit_check_rules
WHERE 1 = 1;
--> To get the Credit Check Againt Customer.
-----------------------------------------------------------------------------------------------
select * from oe_order_headers_all where order_number=56782;
select * from wsh_delivery_details;
select * from oe_order_lines_all;
select * from wsh_delivery_assignments;
select * from ra_customers where Customer_name like 'ABC Corporation Asia';
select * from ra_addresses_all;
select * from ra_site_uses_all;
select * from wsh_new_deliveries;
select OOH.order_number,
OOH.invoice_to_org_id,
RAA_BILL.city bill_to_city,
RAA_SHIP.city ship_to_city
from OE_ORDER_HEADERS_ALL OOH,
RA_SITE_USES_ALL RASU_BILL,
RA_ADDRESSES_ALL RAA_BILL,
RA_SITE_USES_ALL RASU_SHIP,
RA_ADDRESSES_ALL RAA_SHIP
where OOH.order_number = 56782
and OOH.invoice_to_org_id = RASU_BILL.site_use_id
and RASU_BILL.address_id = RAA_BILL.address_id
and OOH.ship_to_org_id = RASU_SHIP.site_use_id
and RASU_SHIP.address_id = RAA_SHIP.address_id;
select OOH.Order_NUmber,
OOL.LINE_NUMBER,
MSI.inventory_item_id,
MSI.description,
RAA_BILL.CITY,
RAA_SHIP.CITY,
WDD.DELIVERY_DETAIL_ID
from OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
RA_CUSTOMERS RAC,
MTL_SYSTEM_ITEMS MSI,
RA_ADDRESSES_ALL RAA_BILL,
RA_ADDRESSES_ALL RAA_SHIP,
RA_SITE_USES_ALL RASU_BILL,
RA_SITE_USES_ALL RASU_SHIP,
WSH_DELIVERY_DETAILS WDD
where OOH.HEADER_ID=OOL.HEADER_ID
and OOH.ORDER_number=56782
and OOL.inventory_item_id=MSI.inventory_item_id
and OOH.invoice_to_org_id=RASU_BILL.site_use_id
and RASU_BILL.address_id=RAA_BILL.address_id
and OOH.ship_to_org_id=RASU_SHIP.site_use_id
and RASU_SHIP.address_id=RAA_SHIP.address_id
and RAC.CUSTOMER_ID=WDD.CUSTOMER_ID
and RAC.PARTY_ID=RAA_BILL.PARTY_ID
and RAC.customer_id=OOL.SOLD_TO_ORG_ID;
select OOH.* from oE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL
where OOH.header_id=ool.header_id
and OOH.ORDER_NUMBER=56782;
select OOHA.ORDER_NUMBER "Order Number",
OOLA. LINE_NUMBER "Line Number",
rc.customer_name "Customer Name",
rsua1.location "Ship to City",
rsua2.location "Bill to City",
oola.inventory_item_id "Item id",
msi.description "Item Description",
wnd.name "Delivery name"
from OE_ORDER_LINES_all OOLA,
OE_ORDER_HEADERS_all OOHA,
ra_customers rc,
RA_SITE_USES_ALL rsua1,
RA_SITE_USES_ALL rsua2,
RA_ADDRESSES_ALL rac,
MTL_SYSTEM_ITEMS msi,
WSH_NEW_DELIVERIES wnd,
WSH_DELIVERY_ASSIGNMENTS wda,
WSH_DELIVERY_DETAILS wdd
WHERE OOLA.HEADER_ID=OOHA.HEADER_ID
and rc.customer_id=ooha.sold_to_org_id
and OOLA.SHIP_TO_ORG_ID=rsua1.SITE_USE_ID
and rsua1.ADDRESS_ID=rac.ADDRESS_ID
and OOLA.INVOICE_TO_ORG_ID=rsua2.SITE_USE_ID
and OOLA.inventory_item_id=msi.inventory_item_id
and oola.org_id=msi.organization_id
and wnd.CUSTOMER_ID=ooha.SOLD_TO_ORG_ID
and rc.CUSTOMER_ID=wnd.CUSTOMER_ID
and wda.DELIVERY_ID=wnd.DELIVERY_ID
and wdd.DELIVERY_DETAIL_ID=wda.DELIVERY_DETAIL_ID
and wda.DELIVERY_ID=wnd.DELIVERY_ID
and wdd.INVENTORY_ITEM_ID=OOLA.inventory_item_id
AND OOHA.order_number=56782;
select * from wsh_new_deliveries;
select ooh.header_id,
ool.line_number,
wdd.customer_id,
rac.customer_name,
ool.ship_to_org_id,
rsua.location,
ool.invoice_to_org_id,
rsua1.location,
wdd.delivery_detail_id,
wdd.inventory_item_id,
wdd.item_description,
wda.delivery_id
from oe_order_headers_all ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
ra_customers rac,
ra_site_uses_all rsua,
ra_site_uses_all rsua1,
wsh_delivery_assignments wda
where 1=1
and ool.header_id=ooh.header_id
and wdd.source_header_id=ooh.header_id
and rac.customer_id=wdd.customer_id
and rsua.site_use_id=ool.ship_to_org_id
and rsua1.site_use_id=ool.invoice_to_org_id;
select ooh.header_id,
ool.line_number,
wdd.customer_id,
rac.customer_name,
ool.ship_to_org_id,
rsua.location,
ool.invoice_to_org_id,
rsua1.location,
wdd.delivery_detail_id,
wdd.inventory_item_id,
wdd.item_description,
wda.delivery_id
from oe_order_headers_all ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
ra_customers rac,
ra_site_uses_all rsua,
ra_site_uses_all rsua1,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where 1=1
and ool.header_id=ooh.header_id
and wdd.source_header_id=ooh.header_id
and rac.customer_id=wdd.customer_id
and rsua.site_use_id=ool.ship_to_org_id
and rsua1.site_use_id=ool.invoice_to_org_id
and wda.delivery_id=wnd.delivery_id
and wdd.delivery_detail_id=wda.delivery_detail_id
and ooh.order_number=56782;
select OOH.Order_NUmber,
OOL.LINE_NUMBER,
RAC.Customer_name,
MSI.inventory_item_id Item_Id,
MSI.description Item_Description,
RASU_BILL.LOCATION Bill_City,
RASU_SHIP.LOCATION Ship_City,
WDD.DELIVERY_DETAIL_ID,
wnd.NAME Delivery_Name
from OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
RA_CUSTOMERS RAC,
MTL_SYSTEM_ITEMS MSI,
RA_SITE_USES_ALL RASU_BILL,
RA_SITE_USES_ALL RASU_SHIP,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_delivery_assignments WDA
where OOH.HEADER_ID=OOL.HEADER_ID
and OOH.ORDER_number=56782
and OOL.inventory_item_id=MSI.inventory_item_id
and RASU_BILL.site_use_id=OOL.invoice_to_org_id
and RASU_SHIP.site_use_id=OOL.ship_to_org_id
and RAC.customer_id=OOH.SOLD_TO_ORG_ID
and WND.delivery_id=wda.delivery_id
and wdd.delivery_detail_id=wda.delivery_detail_id
and wdd.inventory_item_id=msi.inventory_item_id
and wdd.organization_id=msi.organization_id;
select name from wsh_new_deliveries where delivery_id in (select delivery_id from wsh_delivery_assignments where delivery_detail_id=231152);
select *from wsh_delivery_details where source_header_id=94641;
select * from wsh_delivery_assignments where delivery_detail_id=230160;
select *from oe_order_headers_all where order_number=56782;
SELECT *
FROM mtl_material_transactions
WHERE inventory_item_id IN (SELECT inventory_item_id
FROM mtl_system_items
WHERE segment1 LIKE 'kmantri%');
SELECT inventory_item_id, segment1, organization_id
FROM mtl_system_items
WHERE segment1 LIKE 'kmantri%'
ORDER BY oraganization_id;
select * from ar_system_parameters_all;
select * from oe_order_headers_all;
select * from oe_order_lines_all;
select * from wsh_new_deliveries;
SELECT mtlt.lot_number,
msnt.fm_serial_number,
ooha.order_number,
mtrl.line_id
FROM wsh_delivery_details wdd,
oe_order_headers_all ooha,
mtl_txn_request_lines_v mtrl,
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE wdd.source_header_id = ooha.header_id
AND ooha.order_number = 56782
AND mtrl.line_id = wdd.move_order_line_id
AND mtrl.inventory_item_id = 18862
AND mmtt.move_order_line_id = mtrl.line_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id;
SELECT *
FROM mtl_serial_numbers
WHERE inventory_item_id IN (SELECT inventory_item_id
FROM mtl_system_items
WHERE segment1 = 'kmantri_serial')
AND current_organization_id IN (SELECT organization_id
FROM mtl_system_items
WHERE organization_id = 207);
select * from mtl_lot_numbers where inventory_item_id = 606 and organization_id=606;
Select * from fnd_lookups where lookup_code like 'ORG%' order by lookup_code;
SELECT *
FROM wsh_delivery_details
WHERE inventory_item_id IN (SELECT inventory_item_id
FROM mtl_system_items
WHERE segment1 LIKE 'kmantri%');
select * from ar_customers where customer_name like 'Team%';
select inventory_item_id,
organization_id,
transaction_quantity,
subinventory_code
from mtl_material_transactions
where inventory_item_id in(28817, 28818);
select * from mtl_material_transactions;
select * from mtl_system_items where segment1 like 'Item_B';
select line_id, ordered_item, org_id, ordered_quantity, inventory_item_id
from oe_order_lines_all
where header_id=(select header_id
from oe_order_headers_all
where order_number=56813);
select Line_Category_code
from oe_order_lines_all
where inventory_item_id in(28817, 28818);
No comments:
Post a Comment