Query : Sales Order Details After Pick Release

SELECT ooh.order_number,
ool.line_number,
wnd.delivery_id,
mtrh.request_number
FROM oe_order_headers ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
mtl_txn_request_lines mtrl,
mtl_txn_reuest_headers mtrh
WHERE ooh.order_number = --Order Number Input Parameter
AND ool.header_id = ooh.header_id
AND ool.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id

Read More

Query : Sales Order Hold & Release

SELECT OHA.ORDER_NUMBER,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HR.RELEASE_REASON_CODE,
FLV1.MEANING RELEASE_REASON,
HS.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
OH.HEADER_ID,
HS.HOLD_SOURCE_ID
FROM ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV,
ONT.OE_HOLD_RELEASES HR,
APPS.FND_LOOKUP_VALUES FLV1
WHERE HS.ORG_ID = '&ORG_ID' --- xxx
AND HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID = HR.HOLD_RELEASE_ID
AND FLV1.LOOKUP_TYPE = 'RELEASE_REASON'
AND FLV1.VIEW_APPLICATION_ID = 660
AND FLV1.LOOKUP_CODE = HR.RELEASE_REASON_CODE

Read More

Query : INVENTORY & ORDER MANAGEMENT

Just replace xxx in the query with your organization_id

SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1

Read More

Query : Relation between Order Number & Contract Number

Relation between Order Number (From Order Management) and Contract Number (From Service Contracts module).

The relationship is maintained in the table okc_k_rel_objs. following is the sql that will give you the contract number when provided order number.

Release 12 query:

select hdr.contract_number,hdr.contract_number_modifier
from oe_order_headers_all oe,
okc_k_rel_objs rel,
okc_k_headers_all_b hdr
where rel.chr_id = hdr.id
and rel.jtot_object1_code = 'OKX_ORDERHEAD'
and rel.object1_id1 = oe.header_id
and oe.order_number = ''


Release 11i query:

select hdr.contract_number,hdr.contract_number_modifier
from oe_order_headers_all oe,
okc_k_rel_objs rel,
okc_k_headers_b hdr
where rel.chr_id = hdr.id
and rel.jtot_object1_code = 'OKX_ORDERHEAD'
and rel.object1_id1 = oe.header_id
and oe.order_number = ''

Read More