Results for “jai_project_inv_ael_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

JAI_PROJECT_INV_AEL_V is a dictionary view owned by the APPS schema within the JA (Asia/Pacific Localizations) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description defines it as a "Base view for projects." Functionally, the view exposes Project Billing draft invoice accounting entries as they are posted to the General Ledger, presenting them in a flattened structure reminiscent of the GL Accounting Events and Lines (AEL) model. It joins project invoice header data in PA/JAI tables to corresponding GL journal headers and lines, allowing localization users to reconcile localized excise and VAT invoice attributes against the accounting distributions that carry them to the ledger. Because the view is registered in the ETRM with a VALID status, it participates in the standard Oracle supportability and dependency tracking model and can be queried with supported credentialing.

Underlying Base Objects

The documented dependency list includes the following base objects: GL_DAILY_CONVERSION_TYPES, GL_IMPORT_REFERENCES, GL_JE_CATEGORIES, GL_JE_HEADERS, GL_JE_LINES, GL_JE_SOURCES, JAI_PA_DRAFT_INVOICES, PA_CUSTOMERS_V, PA_DRAFT_INVOICES_ALL, and PA_PROJECTS_ALL. All are listed as synonyms except PA_CUSTOMERS_V, which is a view. In the view text, the driving transactional table for the localized invoice attributes is JAI_PA_DRAFT_INVOICES (aliased JPDI), which supplies DRAFT_INVOICE_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE, VAT_INVOICE_NO, and VAT_INVOICE_DATE. Project and customer context is provided by PA_PROJECTS_ALL (P) and PA_CUSTOMERS_V (PC). The GL objects provide the corresponding journal lines: GL_JE_HEADERS (GJH), GL_JE_LINES (GJL), GL_JE_CATEGORIES (GJC), GL_JE_SOURCES (GJS), GL_DAILY_CONVERSION_TYPES (GLCT), and GL_IMPORT_REFERENCES (GJR) for traceability back to the source import. The joins bind journal category, source, and ledger to the project draft invoice, giving one logical accounting row per journal line.

Key Columns

The view exposes APPLICATION_ID (hard-coded as 7000), plus derived columns EVENT_TYPE, TRX_CLASS, TRX_CLASS_NAME, TRX_HDR_ID, and TRX_HDR_TABLE that map accounting events to the JAI_PA_DRAFT_INVOICES source. Standard GL AEL-style columns include JE_HEADER_ID, JE_LINE_NUM, SET_OF_BOOKS_ID (aliased from LEDGER_ID), JE_CATEGORY, USER_JE_CATEGORY_NAME, JE_SOURCE, USER_JE_SOURCE_NAME, ENTERED_CURRENCY, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR, ACCOUNT_ID (from CODE_COMBINATION_ID), and ACCOUNTING_DATE. Currency conversion attributes include CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_TYPE, and CURRENCY_USER_CONVERSION_TYPE. Localization-specific columns include INVOICE_NUMBER, PARTY_NAME, PARTY_NUMBER, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE, VAT_INVOICE_NO, VAT_INVOICE_DATE, EXCISE_REGISTER, PROJECT_NAME, and PROJECT_NUMBER. Critically, the column that addresses the search term vat_invoice_date is VAT_INVOICE_DATE, sourced directly from JPDI.VAT_INVOICE_DATE.

Common Use Cases and Queries

Typical usages include localization reconciliation of VAT/excise invoice dates to GL postings, aging and audit reporting, and integration extracts feeding downstream localization tax reports.

• Locating accounting lines for a VAT invoice date:

  SELECT je_header_id, je_line_num, vat_invoice_no, vat_invoice_date,
         entered_dr, entered_ccr, accounting_date, account_id
  FROM   apps.jai_project_inv_ael_v
  WHERE  vat_invoice_date BETWEEN :from_date AND :to_date;

• Comparing VAT invoice dates against accounting date for cross-period anomalies:

  SELECT project_number, invoice_number, vat_invoice_date, accounting_date
  FROM   apps.jai_project_inv_ael_v
  WHERE  TRUNC(vat_invoice_date) <> TRUNC(accounting_date);

• Reconciling a specific project's localized VAT invoice entries to GL:

  SELECT je_header_id, user_je_category_name, user_je_source_name,
         entered_dr, entered_cr, vat_invoice_no, vat_invoice_date
  FROM   apps.jai_project_inv_ael_v
  WHERE  project_number = :project_number;

These queries leverage the view's dual purpose: presenting project billing accounting lines and local VAT/excise invoice attributes in a single result set suitable for ad-hoc audit and reporting.