Created By:AMARALAM
Created Date:14-09-2012
*********************************

SELECT oeha.ORDER_NUMBER
      ,oeha.header_id
      ,oeha.ORDERED_DATE
      ,oeha.ORDER_TYPE_ID
      ,oeha.PRICE_LIST_ID
      ,oeha.SHIP_FROM_ORG_ID "Organization_Id"
      ,oeha.SOLD_TO_ORG_ID   "Customer_Id"
      ,oeha.SALESREP_ID
      ,oeha.FLOW_STATUS_CODE "Order Status"
      ,oola.ORDERED_ITEM_ID
      ,oola.ORDERED_QUANTITY
      ,oola.SUBINVENTORY
      ,oola.LINE_NUMBER
      ,oola.FREIGHT_CARRIER_CODE
      ,oola.FREIGHT_TERMS_CODE
      ,oola.FLOW_STATUS_CODE "Line Status"
      ,wdd.DELIVER_TO_LOCATION_ID
      ,wdd.DELIVERED_QUANTITY
      ,wdd.CANCELLED_QUANTITY
      ,wdd.RELEASED_STATUS
      ,wda.TYPE "Delivery Type"
      ,wnd.DELIVERED_DATE
      ,wnd.NAME "Delivery Id"
      ,wnd.SHIP_METHOD_CODE
      ,wnd.STATUS_CODE
      ,rcta.TRX_NUMBER
      ,rcta.TRX_DATE
      ,rcta.CUSTOMER_TRX_ID
      ,rctla.QUANTITY_INVOICED
      ,apsa.AMOUNT_APPLIED
      ,gjb.DEFAULT_PERIOD_NAME  "Period Name"
      ,gjb.NAME                 "Batch Name"
      ,gjh.NAME                 "Journal Entry For"
      ,gjh.JE_SOURCE            "Source"
      ,NVL(gjl.ENTERED_CR,0)    "Entered Credit"
      ,NVL(gjl.ENTERED_DR,0)    "Entered Debit"
      ,NVL(gjl.ACCOUNTED_CR,0)  "Accounted Credit"
      ,NVL(gjl.ACCOUNTED_DR,0)  "Accounted Debit"
      ,gjh.CURRENCY_CODE        "Currency"
      ,gjh.STATUS               "Posting Status"
      ,gjh.DATE_CREATED         "Gl Transfor Date"
FROM oe_order_headers_all oeha,
     oe_order_lines_all oola,
     wsh_delivery_details wdd,
     wsh_delivery_assignments wda,
     wsh_new_deliveries wnd,
     ra_customer_trx_all rcta,
     ra_customer_trx_lines_all rctla,
     ra_cust_trx_line_gl_dist_all rgda,
     ar_payment_schedules_all apsa,
     xla_distribution_links xdl,
     xla_events xe,
     xla_ae_headers xah,
     xla_ae_lines xal,
     gl_import_references gir,
     gl_je_lines gjl,
     gl_je_headers gjh,
     gl_je_batches gjb
WHERE 1=1 AND
      (oeha.ORDER_NUMBER BETWEEN :ono1 AND :ono2 ) AND
      oeha.SOLD_TO_ORG_ID=:cust_id AND                      --1005
      oeha.HEADER_ID=oola.HEADER_ID AND
      oola.LINE_ID=wdd.SOURCE_LINE_ID AND
      wdd.DELIVERY_DETAIL_ID=wda.DELIVERY_DETAIL_ID AND
      wda.DELIVERY_ID=wnd.DELIVERY_ID AND
      to_char(oeha.ORDER_NUMBER)=rcta.INTERFACE_HEADER_ATTRIBUTE1 AND
      to_char(wnd.DELIVERY_ID)=rcta.INTERFACE_HEADER_ATTRIBUTE3 AND
      rcta.CUSTOMER_TRX_ID=rctla.CUSTOMER_TRX_ID AND
      to_char(oola.LINE_ID)=rctla.INTERFACE_LINE_ATTRIBUTE6 AND
      rctla.LINE_TYPE='LINE' AND
      rctla.CUSTOMER_TRX_LINE_ID=rgda.CUSTOMER_TRX_LINE_ID AND
      rcta.CUSTOMER_TRX_ID=apsa.CUSTOMER_TRX_ID AND
      rgda.CUST_TRX_LINE_GL_DIST_ID=xdl.SOURCE_DISTRIBUTION_ID_NUM_1 AND
      xdl.SOURCE_DISTRIBUTION_TYPE LIKE 'RA_CUST_TRX_LINE_GL_DIST_ALL' AND
      xdl.APPLICATION_ID=xe.APPLICATION_ID AND
      xdl.EVENT_ID=xe.EVENT_ID AND
      xdl.AE_HEADER_ID=xal.AE_HEADER_ID AND
      xdl.AE_LINE_NUM=xal.AE_LINE_NUM AND
      xe.APPLICATION_ID=xah.APPLICATION_ID AND
      xe.EVENT_ID=xah.EVENT_ID AND
      xah.APPLICATION_ID=xal.APPLICATION_ID AND
      xah.AE_HEADER_ID=xal.AE_HEADER_ID AND
      xal.GL_SL_LINK_ID=gir.GL_SL_LINK_ID AND
      xal.GL_SL_LINK_TABLE=gir.GL_SL_LINK_TABLE AND
      gir.JE_HEADER_ID=gjl.JE_HEADER_ID AND
      gir.JE_LINE_NUM=gjl.JE_LINE_NUM AND
      gjl.JE_HEADER_ID=gjh.JE_HEADER_ID AND
      gjh.JE_BATCH_ID=gjb.JE_BATCH_ID;


Ur's
AmarAlam