Tuesday, 17 January 2017

How to get Query Order number for particular AR Invoice?

How to get Query Order number for particular AR Invoice?

SELECT ooh.order_number
              ,ool.line_number
              ,ool.ordered_item
              ,ool.ordered_quantity * ool.unit_selling_price
              ,rct.trx_number invoice_number
              ,rct.trx_date
              ,rcta.line_number
              ,rcta.unit_selling_price
              ,ooh.org_id
   FROM  oe_order_headers_all ooh
               ,oe_order_lines_all ool
               ,ra_customer_trx_all rct
               ,ra_customer_trx_lines_all rcta
   WHERE ooh.header_id=ool.header_Id
        AND  rcta.interface_line_attribute1=to_char(ooh.order_number)
        AND  rcta.interface_line_attribute6=to_char(ool.line_id)
        AND  rcta.customer_trx_id=rct.customer_trx_id
        AND  ooh.order_number=nvl(:p_order_number,ooh.order_number)
        AND  ooh.org_id=nvl(:p_org_id,ooh.org_id)
        

No comments:

Post a Comment