Sales Invoice Report

select distinct
ooha.order_number, ooha.created_by,ooha.ATTRIBUTE1 Payment_Mode,ooha.BLANKET_NUMBER "Booking Reference",loc.CITY Destination,
rcta.CT_REFERENCE,cust_acct.account_number cust_code,party.party_name cust_name,
ooha.ORDERED_DATE,ooha.ATTRIBUTE7 Truck_NO, ooha.ATTRIBUTE8 Driver_Name,rcta.Attribute5 c_Date ,
rcta.Attribute4 Cheque,rcta.attribute3 Bank , rcta.attribute1 CASH,ooha.TRANSACTIONAL_CURR_CODE Currency,
CASE
WHEN ot.NAME LIKE '%OPC%'
THEN 'OPC'
WHEN ot.NAME LIKE '%SRC%'
THEN 'SRC'
WHEN ot.NAME LIKE '%Clinker%OPC%'
THEN 'Clinker-OPC'
WHEN ot.NAME LIKE '%Clinker%SRC%'
THEN 'Clinker-SRC'
ELSE ot.NAME
END AS PRODUCT_TYPE,
/* 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 oola.ORDER_QUANTITY_UOM = 'TON'
THEN oola.ORDERED_QUANTITY
WHEN oola.ORDER_QUANTITY_UOM = 'BAG'
THEN (oola.ORDERED_QUANTITY*50)
ELSE NULL
END AS sold_ton_quantity,
CASE
WHEN oola.ORDER_QUANTITY_UOM = 'BAG'
THEN oola.ORDERED_QUANTITY
ELSE NULL
END AS sold_bag_quantity,
CASE
WHEN ot.NAME LIKE '%BULK%TONS%'
THEN wsd.shipped_quantity
ELSE NULL
END AS sold_bilk_ton_quantity,
CASE
WHEN ooha.attribute1 LIKE 'Cash'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cash,
CASE
WHEN ooha.attribute1 LIKE 'Check'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cheque,
CASE
WHEN ooha.attribute1 LIKE 'Cash-Transfer'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cash_Transfer,
CASE
WHEN ooha.attribute1 LIKE 'Cash-Deposits'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cash_Deposit,
(oola.unit_selling_price * oola.ordered_quantity)Total,
ship_su.location||' '||loc.ADDRESS1||' '||loc.ADDRESS2||' '||loc.ADDRESS3||' '||loc.POSTAL_CODE ||' '||loc. CITY ||' '||NVL(loc.STATE , loc.PROVINCE) ||' '||loc.COUNTRY Location,
ship_su.SITE_USE_CODE,
/*----------------Dispatch Details----------*/
TO_CHAR (mtrh.date_required, 'HH12:MI AM') time_in,
TO_CHAR (wnd.ultimate_dropoff_date, 'HH12:MI AM') time_out,
wpbv.attribute1 "Empty Weight",
wpbv.attribute2 "empty Weigh Bridge Ref",
wsd.shipped_quantity net_weight,
mmt.ATTRIBUTE1 "Gross weigh M Tons", mmt.ATTRIBUTE4 "Weigh Brid Ref.",
/* wpbv.attribute2 Wt_Bridge_Ref, wpbv.attribute1 Gross_wt_M_Tons,wpbv.TO_SCHEDULED_SHIP_DATE,*/
/*----------------Invoice Details----------------*/
CASE
WHEN oola.ORDER_QUANTITY_UOM LIKE 'TON'
THEN (oola.unit_selling_price)
WHEN oola.ORDER_QUANTITY_UOM LIKE 'BAG'
THEN (oola.unit_selling_price)*(1000/50)
ELSE NULL
END AS "Price Per M Ton (SR)",
rcta.trx_number invoice_number,
(oola.unit_selling_price * oola.ordered_quantity) "Total Amount (SR)" ,
to_char(to_date((oola.ordered_quantity * oola.unit_selling_price),'J'), 'JSP')||' RIYAL ONLY' "Amount in Words" ,
ooha.PAYMENT_TYPE_CODE
from ra_customer_trx_all rcta,
oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all acct,
hz_party_sites party_site ,
hz_locations loc,
oe_transaction_types_tl ot,
wsh_shipping_details_v wsd,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_picking_batches wpb,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
WSH_PICKING_BATCHES_V wpbv,
mtl_material_transactions mmt,
WSH_PICK_SLIP_V WPSV
where
ooha.header_id=oola.header_id
AND ooha.ATTRIBUTE1 in ('Cash','Cash-Transfer','Cash-Deposit','Check')
AND to_char(ooha.order_number)=rcta.CT_REFERENCE(+)
AND ooha.sold_to_org_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
--AND ooha.ship_to_org_id = ship_su.site_use_id(+)
AND ooha.ship_to_org_id = ship_su.site_use_id
AND acct.cust_acct_site_id = ship_su.cust_acct_site_id
and acct.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and cust_acct.party_id = party.party_id
--AND ship_su.SITE_USE_CODE = 'BILL_TO'
AND ooha.order_type_id = ot.transaction_type_id
AND ot.LANGUAGE = USERENV ('LANG')
AND ooha.header_id=wsd.order_header_id
AND oola.line_id=wsd.order_line_id
AND wdd.source_header_id=ooha.header_id
AND wdd.source_line_id=oola.line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id= wnd.delivery_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
and ooha.header_id = wpb.order_header_id(+)
AND ooha.ORDER_NUMBER=wpbv.ORDER_NUMBER(+)
AND oola.LINE_ID=mmt.TRX_SOURCE_LINE_ID
AND wpsv.move_order_line_id =wdd.move_order_line_id
AND wpsv.TRANSACTION_ID=mmt.TRANSACTION_ID
AND ooha.order_number = NVL (:p_order_number, ooha.order_number)
AND party.party_name=nvl(:p_party_name,party.party_name)
AND cust_acct.account_number=nvl(:p_account_number,cust_acct.account_number)
AND trunc(ooha.ORDERED_DATE) between nvl(:p_inv_date_from,ooha.ORDERED_DATE) and nvl(:p_inv_date_to,ooha.ORDERED_DATE)ORDER BY ooha.order_number;
/