Posted By

theonlyalterego on 01/21/12


Tagged

Oracle R12 GL ap SLA AP-SLA-GL r1213


Versions (?)

Oracle AP-SLA-GL Link Query


 / Published in: SQL
 

URL: http://imdjkoch.wordpress.com/2010/12/25/ap-sla-gl-link-query/

AP-SLA-GL Link Query

  1. SELECT
  2. aia.INVOICE_ID "Invoice Id",
  3. aia.INVOICE_NUM "Invoice Number",
  4. aia.INVOICE_DATE "Invoice Date",
  5. aia.INVOICE_AMOUNT "Amount",
  6. xal.ENTERED_DR "Entered DR in SLA",
  7. xal.ENTERED_CR "Entered CR in SLA",
  8. xal.ACCOUNTED_DR "Accounted DR in SLA",
  9. xal.ACCOUNTED_CR "Accounted CR in SLA",
  10. gjl.ENTERED_DR "Entered DR in GL",
  11. gjl.ACCOUNTED_DR "Accounted DR in GL",
  12. xal.ACCOUNTING_CLASS_CODE "Accounting Class",
  13. gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'
  14. ||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'
  15. ||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'
  16. ||gcc.SEGMENT7 "Code Combination",
  17. aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
  18. aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
  19. aia.GL_DATE "GL Date",
  20. xah.PERIOD_NAME "Period",
  21. aia.PAYMENT_METHOD_CODE "Payment Method",
  22. aia.VENDOR_ID "Vendor Id",
  23. aps.VENDOR_NAME "Vendor Name",
  24. xah.JE_CATEGORY_NAME "JE Category Name"
  25. FROM
  26. ap.ap_invoices_all aia,
  27. xla.xla_transaction_entities XTE,
  28. xla.xla_events xev,
  29. xla.xla_ae_headers XAH,
  30. xla.xla_ae_lines XAL,
  31. GL_IMPORT_REFERENCES gir,
  32. gl_je_headers gjh,
  33. gl_je_lines gjl,
  34. gl_code_combinations gcc,
  35. ap.ap_suppliers aps,
  36. (SELECT aid1.invoice_id,
  37. pa.project_id,
  38. nvl(pa.segment1,'NO PROJECT') Project
  39. FROM ap_invoice_distributions_all aid1,
  40. PA_PROJECTS_ALL pa
  41. WHERE aid1.rowid IN
  42. (SELECT MAx(rowid)
  43. FROM ap_invoice_distributions_all aid2
  44. WHERE aid1.INvoice_ID=aid2.INvoice_ID
  45. GROUP BY aid1.invoice_id)
  46. AND aid1.project_id=pa.project_id(+)) sql1,
  47. (SELECT aid1.invoice_id,
  48. pt.task_id,
  49. nvl(pt.task_number,'NO TASK') Task
  50. FROM ap_invoice_distributions_all aid1,
  51. PA_TASKS pt
  52. WHERE aid1.rowid IN
  53. (SELECT MAx(rowid)
  54. FROM ap_invoice_distributions_all aid2
  55. WHERE aid1.INvoice_ID=aid2.INvoice_ID
  56. GROUP BY aid1.invoice_id)
  57. AND aid1.task_id=pt.task_id(+)) sql2
  58. WHERE
  59. aia.INVOICE_ID = xte.source_id_int_1
  60. AND aia.INVOICE_ID=sql1.Invoice_ID
  61. AND aia.INVOICE_ID=sql2.Invoice_ID
  62. AND xev.entity_id= xte.entity_id
  63. AND xah.entity_id= xte.entity_id
  64. AND xah.event_id= xev.event_id
  65. AND XAH.ae_header_id = XAL.ae_header_id
  66. AND XAH.je_category_name = 'Purchase Invoices'
  67. AND XAH.gl_transfer_status_code= 'Y'
  68. AND XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
  69. AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
  70. AND gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
  71. AND gjh.JE_HEADER_ID=gir.JE_HEADER_ID
  72. AND gjl.JE_HEADER_ID=gir.JE_HEADER_ID
  73. AND gir.JE_LINE_NUM=gjl.JE_LINE_NUM
  74. AND gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
  75. AND gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
  76. AND aia.VENDOR_ID=aps.VENDOR_ID
  77. AND gjh.STATUS='P'
  78. AND gjh.Actual_flag='A'
  79. AND gjh.CURRENCY_CODE='USD'
  80. AND aia.Invoice_id=&Invoice_Id;

Report this snippet  

You need to login to post a comment.