Introduction:
This Post illustrates the steps required to Fetch Open sales orders in Oracle EBS.
Script to Fetch the Open sales Orders
SELECT customer_number, customer_name, organization_code, sku,
request_date, schedule_ship_date, ordered_quantity,
(ordered_quantity * unit_selling_price) extended_amount, order_type_id,
order_quantity_uom, unit_selling_price,
TRUNC (last_update_date) last_updated_date, -1 last_updated_by,
order_number, ordered_date, header_id, line_id, inventory_item_id,
operating_unit, organization_id
FROM (SELECT hca.account_number customer_number,
hp.party_name customer_name, ood.organization_code,
oh.order_number, msib.segment1 sku,
TRUNC (ool.request_date) request_date,
TRUNC (ool.schedule_ship_date) schedule_ship_date,
oh.order_type_id, ool.order_quantity_uom, ool.last_update_date,
ool.creation_date,
CASE
WHEN ool.order_quantity_uom = ‘EA’
THEN ool.ordered_quantity
ELSE NVL (ROUND ((ool.ordered_quantity), 2),
0
)
* NVL (muc.conversion_rate, 0)
END ordered_quantity,
ool.unit_selling_price, oh.ordered_date, oh.header_id,
ool.line_id, msib.inventory_item_id, oh.org_id operating_unit,
oh.ship_from_org_id organization_id
FROM oe_order_headers_all oh,
oe_order_lines_all ool,
mtl_system_items_b msib,
org_organization_definitions ood,
hz_cust_accounts hca,
hz_parties hp,
mtl_uom_conversions muc
WHERE oh.open_flag = ‘Y’
AND ool.open_flag = ‘Y’
AND oh.org_id IN (81, 82)
AND oh.header_id = ool.header_id
AND msib.inventory_item_id = ool.inventory_item_id
AND msib.organization_id = oh.ship_from_org_id
AND ood.organization_id = oh.ship_from_org_id
AND hca.cust_account_id = oh.sold_to_org_id
AND hp.party_id = hca.party_id
AND ool.inventory_item_id = muc.inventory_item_id)
Queries
Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions
