-

  1. Oracle AP-SLA-GL Link Query

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

Comments

Post a Comment

Popular posts from this blog

AP Invoice Rejection - Errors

-
Account Required.  Accounting information is missing for this line. Acct Date Beyond Enc Year.  Accounting date (ACCOUNTING_DATE) is beyond the encumbrance year and the Use PO Encumbrance Financials option is enabled. Acct Date Not In Open Pd.  Accounting date (ACCOUNTING_DATE) is not in an open period. Amount Billed Would Be Below Zero.  This transaction would reduce the amount billed on the purchase order to below zero. Amount Unpaid Too Small.  The prepayment amount that you are trying to apply (PREPAY_APPLY_AMOUNT) exceeds the invoice amount. Note:  For invoices that include prepayments (that is, the invoice amount has already been reduced by the amount of the prepayment) the prepayment amount cannot exceed the unpaid invoice amount. Applying More Than Avail.  You are applying a prepayment to the invoice and the amount you are applying is greater than the prepayment amount available for application. You can see a prepayment’s available a...
Read more

AP Invoice Rejections

-
Select aii.Invoice_num , air.PARENT_TABLE, air.REJECT_LOOKUP_CODE, air.parent_id From apps. AP_INTERFACE_REJECTIONS air, apps. AP_INVOICE_LINES_INTERFACE aili, apps. AP_INVOICES_INTERFACE aii WHere aii.invoice_id=aili.invoice_id AND  aili.invoice_id=air.parent_id AND aii.Invoice_num = ‘Invoice Number’ aii.INVOICE_ID, aii.SOURCE, aii.invoice_num, (select decode(count(*),0,'Invoice N/A','Invoice Available') from ap_invoices_all where org_id = aii.ORG_ID and invoice_num = aii.INVOICE_NUM) Inv_in_Base, aii.CREATION_DATE, aii.invoice_date,  aii.GL_DATE, ail.LINE_NUMBER, aii.REQUEST_ID, aii.invoice_currency_code, nvl(airh.REJECT_LOOKUP_CODE,airl.REJECT_LOOKUP_CODE) Rejection_Reason, (select decode(count(INACTIVE_DATE),0,'Site is Active','Site is Inactive') from po_vendor_sites_All where vendor_site_id = aii.VENDOR_SITE_ID) Site_STatus from ap_invoice_lines_interface ail, AP_INTERFACE_REJECTIONS airh, AP_INTERFACE_REJECTIONS airl, ap_invoices_interface aii,...
Read more

GL Interface

-
STATUS NOT NULL VARCHAR2(50)   LEDGER_ID   NUMBER(15)   ACCOUNTING_DATE NOT NULL DATE   CURRENCY_CODE NOT NULL VARCHAR2(15)   DATE_CREATED NOT NULL DATE   CREATED_BY NOT NULL NUMBER(15)   ACTUAL_FLAG NOT NULL VARCHAR2(1)   USER_JE_CATEGORY_NAME NOT NULL VARCHAR2(25)   USER_JE_SOURCE_NAME NOT NULL VARCHAR2(25)   CURRENCY_CONVERSION_DATE   DATE   ENCUMBRANCE_TYPE_ID   NUMBER   BUDGET_VERSION_ID   NUMBER   USER_CURRENCY_CONVERSION_TYPE   VARCHAR2(30)   CURRENCY_CONVERSION_RATE   NUMBER   AVERAGE_JOURNAL_FLAG   VARCHAR2(1)   ORIGINATING_BAL_SEG_VALUE   VARCHAR2(25)   SEGMENT1   VARCHAR2(25) ...
Read more