Home » Applications » Oracle Fusion Apps & E-Business Suite » How to get sales order data with GL tables (Oracle EBS 11, MS XP)
How to get sales order data with GL tables [message #553291] Thu, 03 May 2012 04:30
anna marie
Messages: 26
Registered: June 2006
Location: Philippines
Junior Member
I want to get sales order details from my GL query. I need to get item number, quantity and customer PO from there. Here is my query from GL.


SELECT PH.po_header_id,
  I.GL_DATE,
  R.je_header_id JE_HEADER_ID,
  R.je_line_num JE_LINE_NUM,
  aeh.org_id ORG_ID ,
  I.invoice_num TRX_NUMBER_C ,
  ael.description COMMENTS ,
  ael.ACCOUNTED_CR ACCOUNTED_CR,
  ael.ACCOUNTED_DR ACCOUNTED_DR,
  ael.code_combination_id CODE_COMBINATION_ID,
  ael.currency_code CURRENCY_CODE,
  ael.entered_cr ENTERED_CR,
  ael.entered_dr ENTERED_DR,
  PH.segment1 PO_ORDER_NUMBER,
  GL.segment1 company,
  GL.segment2,
  GL.segment3,
  GL.segment4,
  GL.segment5 account
  ,ael.description
  ,PD.quantity_ordered, PD.amount_billed, D.quantity_invoiced,  D.unit_price
FROM gl_import_references R, --ok
  gl_je_categories jc,
  ap_ae_lines_all AEL, --ok
  ap_ae_headers_all AEH, --ok
  ap_invoice_distributions_all D,
  ap_invoices_all I,
  ap_accounting_events_all AE,
  po_releases_all PR,
  po_headers_all PH,
  po_distributions_all PD,
  po_vendor_sites_all VS,
  gl_code_combinations GL
WHERE 1=1
AND jc.je_category_name       = aeh.ae_category
AND PR.po_release_id(+)       = PD.po_release_id
AND PH.po_header_id(+)        = PD.po_header_id
AND PD.po_distribution_id(+)  = D.po_distribution_id
AND ael.third_party_sub_id    = VS.vendor_site_id
AND AE.accounting_event_id    = AEH.accounting_event_id
AND I.invoice_id              = AE.source_id
AND AE.source_table           = 'AP_INVOICES'
AND AEH.ae_header_id          = AEL.ae_header_id
AND DECODE(ael.source_table,'AP_INVOICE_DISTRIBUTIONS', AEL.source_id,NULL) = D.invoice_distribution_id(+)
AND AEL.gl_sl_link_id         = R.gl_sl_link_id
and ael.code_combination_id = GL.code_combination_id

[Updated on: Thu, 03 May 2012 04:34]

Report message to a moderator

Previous Topic: EBS Migrate on another platform
Next Topic: How to disable the NEW menu item
Goto Forum:
  


Current Time: Fri Nov 28 21:50:37 CST 2014

Total time taken to generate the page: 0.08831 seconds