Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick, Simple and Absolutely Free.

Thread Rating:
  • 6 Vote(s) - 2.83 Average
  • 1
  • 2
  • 3
  • 4
  • 5
AP Inoice track to GL
#1
Select
GJH.NAME,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
aia.INVOICE_ID "Invoice Id",
     aia.INVOICE_NUM "Invoice Number",
     aia.INVOICE_DATE "Invoice Date",
     aia.INVOICE_AMOUNT "Amount",
     xal.ENTERED_DR "Entered DR in SLA",
     xal.ENTERED_CR "Entered CR in SLA",
     xal.ACCOUNTED_DR "Accounted DR in SLA",
     xal.ACCOUNTED_CR "Accounted CR in SLA",
      gjh.je_source ,
     gjl.ENTERED_DR "Entered DR in GL",
     gjl.ENTERED_CR "Entered CR in GL",
     gjl.ACCOUNTED_DR "Accounted DR in GL",
     gjl.ACCOUNTED_CR "Accounted CR in GL",
     xal.ACCOUNTING_CLASS_CODE "Accounting Class",
     gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'
         ||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'
         ||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'
         ||gcc.SEGMENT7 "GL_Code Combination",
     aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
     aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
     aia.GL_DATE "GL Date",
     xah.PERIOD_NAME "Period",
     aia.PAYMENT_METHOD_CODE "Payment Method",
     aia.VENDOR_ID "Vendor Id",
     aps.VENDOR_NAME "Vendor Name",
     xah.JE_CATEGORY_NAME "JE Category Name"
FROM
     apps.ap_invoices_all aia,
     xla.xla_transaction_entities XTE,
     apps.xla_events xev,
     apps.xla_ae_headers XAH,
     apps.xla_ae_lines XAL,
     apps.GL_IMPORT_REFERENCES gir,
     apps.gl_je_headers gjh,
     apps.gl_je_lines  gjl,
     apps.gl_code_combinations gcc,
     apps.ap_suppliers aps
 Where
aia.INVOICE_ID = xte.source_id_int_1
and xev.entity_id= xte.entity_id
     and xah.entity_id= xte.entity_id
     and xah.event_id= xev.event_id
     and XAH.ae_header_id = XAL.ae_header_id
--     and XAH.gl_transfer_status_code= 'Y'
     and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
     and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
     and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
     and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
     and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
     and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
     and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
     and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
    and aia.VENDOR_ID=aps.VENDOR_ID
and aia.INVOICE_DATE between :Invoice_from and :invoice_to
--   and gcc.segment1 = 01 
--     and gjh.STATUS='P'
     and gjh.Actual_flag='A'
 
Reply
Thanks given by:


Possibly Related Threads...
Thread Author Replies Views Last Post
  Validate and track errors during import journal? Santosh 0 623 08-31-2014, 04:46 PM
Last Post: Santosh
  Track supplier’s information from legacy to oracle apps. Ali Rizwan 2 865 08-18-2014, 07:38 PM
Last Post: Ali Rizwan



Users browsing this thread: 1 Guest(s)
OTech Talks, Lets Talk OPEN! Get Oracle Tutorials, Tips! Post a Question!