Below is the SQL to get sales order export from Fusion Apps Order Mgmt Cloud. Please use Oracle Transaction Business Intelligence (OTBI) Data model (SQL) and Report to create the report. I have used the script extensively to validated the converted Sales Orders.
SELECT (SELECT name FROM fusion.hr_operating_units where organization_id = h.org_id) BU_NAME,
h.order_number,h.SOURCE_REVISION_NUMBER revision_NUM, — h.header_id,
(select party_name from fusion.hz_parties where party_id = h.sold_to_Party_id) Customer,
(select account_number from fusion.hz_cust_accounts a where party_id = h.sold_to_Party_id) Customer_Acct,
h.sold_to_contact_id Contact,
h.ordered_date,
h.customer_po_number PO_NUMBER,
h.order_type_code,
h.status_code Heder_Status, h.open_flag,h.canceled_flag,
–h.sold_to_customer_id,
h.transactional_currency_code CURRENCY,
h.source_order_number,
h.source_order_system,
–h.orig_sys_document_ref,
(select party_name from fusion.DOO_ORDER_ADDRESSES_V a, fusion.hz_cust_accounts hca, fusion.hz_parties hp
where a.address_use_type = ‘BILL_TO’ AND a.header_id = H.header_id and a.cust_acct_id = hca.cust_account_id AND hca.party_id = hp.party_id ) Bill_To_Customer ,
(SELECT cust_acct_site_use_id
FROM fusion.DOO_ORDER_ADDRESSES_V a
WHERE a.header_id = h.header_id
AND a.address_use_type = ‘BILL_TO’) Bill_TO_cust_acct_site_use_id,
(SELECT Address1||’ ‘||Address2||’ ‘||Address3||’ ‘||Address4||’ ‘||city||’ ‘||county||’ ‘||NVL(STATE,PROVINCE)||’ ‘||Postal_code||’ ‘||COUNTRY
FROM fusion.DOO_ORDER_ADDRESSES_V a, fusion.hz_cust_site_uses_all hcsu, fusion.hz_cust_acct_sites_all hcas, fusion.hz_party_sites hps, fusion.hz_locations hl
WHERE a.header_id = h.header_id
AND a.address_use_type = ‘BILL_TO’
AND hcsu.site_use_id = a.cust_acct_site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_ID ) Bill_TO_Addr,
(select party_name from fusion.DOO_ORDER_ADDRESSES_V a, fusion.hz_parties hp where a.address_use_type = ‘SHIP_TO’ AND a.header_id = h.header_id and a.party_id = hp.party_id) SHIP_TO_Customer,
(SELECT a.party_site_id
FROM fusion.DOO_ORDER_ADDRESSES_V a
WHERE a.header_id = h.header_id
AND a.address_use_type = ‘SHIP_TO’) Ship_To_Party_Site_id,
fl.SHIP_TO_PARTY_SITE_ID line_ship_to_party_site_id,
(SELECT Address1||’ ‘||Address2||’ ‘||Address3||’ ‘||Address4||’ ‘||city||’ ‘||county||’ ‘||NVL(STATE,PROVINCE)||’ ‘||Postal_code||’ ‘||COUNTRY
FROM fusion.DOO_ORDER_ADDRESSES_V a, fusion.hz_party_sites hps, fusion.hz_locations hl
WHERE a.header_id = h.header_id
AND a.address_use_type = ‘SHIP_TO’
AND a.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_ID) Ship_To_Addr,
HEFF.attribute_char1, HEFF.attribute_char2, HEFF.attribute_char3,
h.created_by, h.creation_date,
–l.display_line_number, l.line_id,
l.line_number,–l.source_line_number,
–l.inventory_item_id,
(SELECT ITEM_NUMBER FROM fusion.EGP_SYSTEM_ITEMS WHERE INVENTORY_ITEM_ID = l.inventory_item_id and organization_id=l.inventory_organization_id) “Item”,
–l.inventory_organization_id, l.ordered_qty,l.ordered_uom,l.schedule_ship_date,l.unit_selling_price,l.Unit_list_Price, l.canceled_flag,l.canceled_qty,l.shipped_qty
l.ordered_qty,l.ORDERED_UOM,l.Unit_Selling_Price,l.extended_amount,
l.line_type_code, l.status_code line_status,
(SELECT MIN(b.segment1) FROM fusion.DOO_PROJECTS a, FUSION.PJF_PROJECTS_ALL_B b
where a.parent_entity_code =’LINE’ and a.parent_entity_id = fl.fulfill_line_id and a.pjc_project_id = b.project_id) PROJECT_num,
(SELECT MIN(a.organization_code) FROM fusion.inv_org_parameters a, fusion.DOO_FULFILL_LINES_ALL fl where a.organization_id = fl.FULFILL_ORG_ID and fl.line_id = l.line_id) Warehouse,
(sELECT distinct PartyPEO.party_name|| ‘-‘|| ModeOfTransportPEO.MEANING ||’-‘||ServiceLevelPEO.MEANING “Ship Method”
FROM fusion.msc_xref_mapping mxm_LOS ,
fusion.msc_xref_mapping mxm_MOT ,
fusion.msc_xref_mapping mxm_CAR ,
fusion.RCS_LOOKUPS ModeOfTransportPEO ,
fusion.RCS_LOOKUPS ServiceLevelPEO ,
fusion.HZ_PARTIES PartyPEO ,
fusion.WSH_ORG_CARRIER_SERVICES wogs
WHERE ( fl.SHIP_CLASS_OF_SERVICE = mxm_LOS.target_value AND mxm_LOS.entity_name = ‘WSH_SERVICE_LEVELS’)
AND (ServiceLevelPEO.LOOKUP_CODE = MXM_LOS.SOURCE_VALUE AND ServiceLevelPEO.LOOKUP_TYPE = ‘WSH_SERVICE_LEVELS’ )
AND ( fl.SHIP_MODE_OF_TRANSPORT = mxm_MOT.target_value AND mxm_MOT.entity_name = ‘WSH_MODE_OF_TRANSPORT’)
AND (ModeOfTransportPEO.LOOKUP_CODE = MXM_MOT.SOURCE_VALUE AND ModeOfTransportPEO.LOOKUP_TYPE = ‘WSH_MODE_OF_TRANSPORT’)
AND (TO_CHAR(fl.CARRIER_ID) = mxm_CAR.target_value AND mxm_CAR.entity_name = ‘CARRIERS’)
AND PartyPEO.party_id = mxm_CAR.source_value
AND (TO_CHAR(wogs.CARRIER_ID) = MXM_CAR.SOURCE_VALUE
AND wogs.SERVICE_LEVEL = MXM_LOS.SOURCE_VALUE
AND wogs.MODE_OF_TRANSPORT = MXM_MOT.SOURCE_VALUE)) Shipping_Method,
(SELECT meaning FROM fusion.MSC_SR_LOOKUP_VALUES_VL where lookup_type = ‘FOB’ and lookup_code = fl.FOB_POINT_CODE)IncoTerms,
(SELECT meaning FROM fusion.MSC_SR_LOOKUP_VALUES_VL a where lookup_type = ‘WSH_FREIGHT_CHARGE_TERMS’ and lookup_code = fl.FREIGHT_TERMS_CODE) FreightTerms,
fl.request_ship_date, fl.schedule_ship_date, fl.promise_ship_date,
fl.customer_po_line_number
(select name FROM fusion.RA_TERMS where term_id = DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE(‘TERM_ID’, ‘PAYMENT_TERMS’, fl.payment_term_id)) PaymentTerm
FROM fusion.DOO_HEADERS_ALL_V h, fusion.DOO_LINES_ALL_V l, fusion.DOO_HEADERS_EFF_B HEFF, fusion.DOO_FULFILL_LINES_ALL fl
WHERE h.header_id = l.header_id
AND fl.line_id = l.line_id
and h.header_id = heff.header_id
— AND h.source_order_system = ‘LEGACY’
ORDER BY 1,2, l.line_number