Introduction:
This Post illustrates the steps required to fetch the Sales History Data in Oracle EBS.
Script:
SELECT customer_number, customer_name, organization_code, order_number,
line_number, sku, invoice_number, invoice_date,
quantity_invoiced, unit_selling_price,
(invoiced_quantity * unit_selling_price) extended_amount,
order_type_id, header_id, line_id, inventory_item_id, customer_trx_id,
customer_trx_line_id, operating_unit, organization_id
FROM (SELECT DISTINCT hca.account_number customer_number, a.order_type_id,
hp.party_name customer_name,
(SELECT ood.organization_code
FROM org_organization_definitions ood
WHERE ood.organization_id = a.ship_from_org_id)
organization_code,
a.order_number, segment1 sku,
e.trx_number invoice_number, b.unit_selling_price,
TRUNC (e.creation_date) invoice_date,
DECODE (b.order_quantity_uom,
‘EA’, b.invoiced_quantity,
b.invoiced_quantity * h.conversion_rate
) quantity_invoiced,
b.line_number || ‘.’ || b.shipment_number line_number,
b.invoiced_quantity, a.header_id, b.line_id,
c.inventory_item_id, e.customer_trx_id,
f.customer_trx_line_id, a.org_id operating_unit,
a.ship_from_org_id organization_id
FROM oe_order_headers_all a,
oe_order_lines_all b,
mtl_system_items_b c,
ra_customer_trx_all e,
ra_customer_trx_lines_all f,
hz_cust_accounts hca,
hz_parties hp,
mtl_uom_conversions h
WHERE 1 = 1
AND a.header_id = b.header_id
AND c.inventory_item_id = b.inventory_item_id
AND c.organization_id = a.ship_from_org_id
AND e.customer_trx_id = f.customer_trx_id
AND e.bill_to_customer_id = a.sold_to_org_id
AND TO_CHAR (a.order_number) = f.sales_order
AND a.ship_from_org_id = f.warehouse_id
AND f.warehouse_id = c.organization_id
AND f.inventory_item_id = c.inventory_item_id
AND f.interface_line_attribute6 = b.line_id
AND hca.cust_account_id = a.sold_to_org_id
AND hp.party_id = hca.party_id
AND b.inventory_item_id = h.inventory_item_id
AND a.org_id = :p_org_id
AND (f.extended_amount <> 0 OR f.extended_amount = 0))
Queries
Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions
