QUERY FOR AP - XLA - GL RELATION IN EBS R12

SELECT GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  SUM(xla_ae_lines.ENTERED_DR),

  SUM(xla_ae_lines.ENTERED_CR),

  SUM(xla_ae_lines.ACCOUNTED_DR),

  SUM(xla_ae_lines.ACCOUNTED_CR),

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  GL_JE_HEADERS.JE_CATEGORY,

  GL_JE_HEADERS.JE_SOURCE,

  GL_JE_HEADERS.NAME,

  GL_JE_LINES.REFERENCE_1,

  GL_JE_LINES.REFERENCE_3,

  GL_JE_LINES.STATUS,

  GL_JE_LINES.REFERENCE_5,

  GL_JE_LINES.LAST_UPDATE_DATE

FROM xla_ae_headers,

  xla_ae_lines,

  AP_ACCOUNTING_EVENTS_ALL,

  AP_INVOICES_ALL,

  AP_LOOKUP_CODES,

  AP_INVOICE_DISTRIBUTIONS_ALL,

  PO_DISTRIBUTIONS_ALL,

  RCV_TRANSACTIONS RCV_TRANSACTIONS1,

  PO_RELEASES_ALL,

  PO_HEADERS_ALL,

  RCV_SHIPMENT_HEADERS,

  GL_JE_CATEGORIES_VL,

  gl_import_references,

  gl_je_lines,

  GL_JE_HEADERS,

  GL_SETS_OF_BOOKS,

  PO_VENDORS

WHERE 1=1

  --AND xla_ae_headers.je_category_name IN ('Payments','Purchase Invoices','Reconciled Payments'

AND GL_JE_HEADERS.JE_HEADER_ID=173521

AND TRUNC(gl_je_lines.last_update_date) BETWEEN TO_DATE('01-MAY-2014','DD-MON-YYYY')

AND TO_DATE('31-MAY-2014','DD-MON-YYYY')

AND xla_ae_lines.ae_header_id                                                                = xla_ae_headers.ae_header_id

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_TABLE                                                   = 'AP_INVOICES')

AND (AP_ACCOUNTING_EVENTS_ALL.ACCOUNTING_EVENT_ID                                            =xla_ae_headers.EVENT_ID)

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_ID                                                      = AP_INVOICES_ALL.INVOICE_ID)

AND (AP_LOOKUP_CODES.LOOKUP_CODE                                                             =AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE)

AND (AP_LOOKUP_CODES.LOOKUP_TYPE                                                             ='INVOICE TYPE')

AND (DECODE(XLA_AE_LINES.SOURCE_TABLE,'AP_INVOICE_DISTRIBUTIONS',XLA_AE_LINES.SOURCE_ID,NULL)=AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID                                         =PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID                                         =RCV_TRANSACTIONS1.TRANSACTION_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_RELEASE_ID                                                      =PO_RELEASES_ALL.PO_RELEASE_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_HEADER_ID                                                       =PO_HEADERS_ALL.PO_HEADER_ID(+))

AND (RCV_TRANSACTIONS1.SHIPMENT_HEADER_ID                                                    =RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID(+))

AND (XLA_AE_HEADERS.JE_CATEGORY_NAME                                                         = GL_JE_CATEGORIES_VL.JE_CATEGORY_NAME)

AND (XLA_AE_LINES.GL_SL_LINK_ID                                                              =gl_import_references.GL_SL_LINK_ID)

AND gl_import_references.je_header_id                                                        = gl_je_lines.je_header_id

AND gl_import_references.je_line_num                                                         = gl_je_lines.je_line_num

AND (GL_JE_HEADERS.JE_HEADER_ID                                                              = GL_JE_LINES.JE_HEADER_ID)

AND (GL_JE_LINES.LEDGER_ID                                                                   =GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID)

AND (XLA_AE_LINES.PARTY_ID                                                                   = PO_VENDORS.VENDOR_ID)

GROUP BY GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  GL_JE_HEADERS.JE_CATEGORY,

  GL_JE_HEADERS.JE_SOURCE,

  GL_JE_HEADERS.NAME,

  GL_JE_LINES.REFERENCE_1,

  GL_JE_LINES.REFERENCE_3,

  GL_JE_LINES.STATUS,

  GL_JE_LINES.REFERENCE_5,

  GL_JE_LINES.LAST_UPDATE_DATE ;