--Query used to display the Order details and its invoice details
select ooh.ORDER_NUMBER "Order Number"
, ra.CUSTOMER_NUMBER "Customer Number"
, ra.CUSTOMER_NAME "Customer Name"
,wnd.SOURCE_HEADER_ID "Delivery Number"
,ool.LINE_ID "Order LineID"
,ool.LINE_NUMBER "Order Line Number"
,rct.TRX_NUMBER "Invoice Number"
,rct.TRX_DATE "Invoice Date"
,ool.ORDERED_QUANTITY "Ordered Quantity"
,ool.SHIPPED_QUANTITY "Shipped Quantity"
,rctl.QUANTITY_INVOICED "Invoiced Quantity"
,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE "Ordered Amount"
,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE "Invoiced Amount"
,(select sum(l.ORDERED_QUANTITY*l.UNIT_SELLING_PRICE)
from oe_order_headers_all h
,oe_order_lines_all l
where h.HEADER_ID in l.HEADER_ID
and h.HEADER_ID=ooh.HEADER_ID
group by h.HEADER_ID ) "Sub Total"
from oe_order_headers_all ooh
,oe_order_lines_all ool
,ra_customers ra
,wsh_new_deliveries wnd
,ra_customer_trx_all rct
,ra_customer_trx_lines_all rctl
where 1=1
and ooh.ORDER_NUMBER = '66415'
and ooh.HEADER_ID = ool.HEADER_ID
and ra.CUSTOMER_ID = ooh.SOLD_TO_ORG_ID
and ooh.HEADER_ID = wnd.SOURCE_HEADER_ID(+)
--and ra.CUSTOMER_ID = wnd.CUSTOMER_ID
and to_char(ooh.ORDER_NUMBER) = rct.CT_REFERENCE
and rct.SOLD_TO_CUSTOMER_ID = ra.CUSTOMER_ID
and rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID
and rctl.SHIP_TO_CUSTOMER_ID = ra.CUSTOMER_ID
group by ooh.HEADER_ID
,ooh.ORDER_NUMBER
, ra.CUSTOMER_NUMBER
, ra.CUSTOMER_NAME
,wnd.SOURCE_HEADER_ID
,ool.LINE_ID
,ool.LINE_NUMBER
,rct.TRX_NUMBER
,rct.TRX_DATE
,ool.ORDERED_QUANTITY
,ool.SHIPPED_QUANTITY
,rctl.QUANTITY_INVOICED
,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE
,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE
No comments:
Post a Comment
Text Message