Thursday, April 16, 2020

Sales order query ,Order_amount , invoice_amount oracle apps r12 sql query to find order amount

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

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...