Sales Order Details

SELECT ooh.order_number,
trunc(ooh.ordered_date),
cust.ACCOUNT_NUMBER customer_number
,ott.NAME order_type
, hp_bill.party_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
||hl_ship.address2||Decode(hl_ship.address3,NULL,' ',chr(10))
||hl_ship.address3||Decode(hl_ship.address4,NULL,' ',chr(10))
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',')
||hl_ship.state ||Decode(hl_ship.postal_code,'',',')
||hl_ship.postal_code ship_to_address
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
||hl_bill.address2||Decode(hl_bill.address3,NULL,' ',chr(10))
||hl_bill.address3||Decode(hl_bill.address4,NULL,' ',chr(10))
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',')
||hl_bill.state ||Decode(hl_bill.postal_code,'',',')
||hl_bill.postal_code bill_to_address
, ooh.transactional_curr_code currency_code
, mp.organization_code,
CASE
WHEN ot.NAME LIKE '%Bag%'
THEN wsd.requested_quantity
ELSE NULL
END AS sold_bag_quantity,
CASE
WHEN ot.NAME LIKE '%Tons%'
THEN wsd.shipped_quantity
ELSE NULL
END AS sold_ton_quantity,
CASE
WHEN ot.NAME LIKE '%Bulk%Tons%'
THEN wsd.shipped_quantity
ELSE NULL
END AS sold_bilk_ton_quantity,
CASE
WHEN ooh.PAYMENT_TYPE_CODE LIKE 'Cash'
THEN nvl(ool.ordered_quantity,0) * nvl(ool.unit_selling_price,0)
ELSE NULL
END AS Pay_Cash,
CASE
WHEN ooh.PAYMENT_TYPE_CODE LIKE 'Check'
THEN nvl(ool.ordered_quantity,0) * nvl(ool.unit_selling_price,0)
ELSE NULL
END AS Pay_Check,
ooh.CHECK_NUMBER,
ooh.fob_point_code
, ooh.freight_terms_code
, ooh.cust_po_number
FROM oe_order_headers_all ooh
, oe_order_lines_all ool
, oe_transaction_types_tl ott
, oe_transaction_types_tl ot
, wsh_shipping_details_v wsd
, hz_cust_site_uses_all hcs_ship
, hz_cust_accounts cust
, hz_cust_acct_sites_all hca_ship
, hz_party_sites hps_ship
, hz_parties hp_ship
, hz_locations hl_ship
, hz_cust_site_uses_all hcs_bill
, hz_cust_acct_sites_all hca_bill
, hz_party_sites hps_bill
, hz_parties hp_bill
, hz_locations hl_bill
, mtl_parameters mp
WHERE 1 = 1
--AND header_id = :p_header_id
AND ooh.header_id = ool.header_id
AND ooh.order_type_id = ott.transaction_type_id
ANd ooh.order_type_id = ot.transaction_type_id
AND wsd.order_header_id = ooh.header_id
AND wsd.order_line_id = ool.line_id
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND cust.cust_account_id = hca_ship.cust_account_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id ;