Search Results gl_import_references_n3

Overview

JAI_PO_REC_AEL_V is an APPS-owned database view within the Oracle E-Business Suite Release 12.1.1 and 12.2.2 localization layer for the Asia/Pacific region (product group JA). Its documented purpose is to serve as the base view of PO receipt accounting events. The view consolidates general ledger accounting entries generated by Purchasing and Receiving activity into a drill-down reporting structure, exposing the linkage between a receipt transaction, its accounting lines in GL, and the supporting receipt header and line attributes. It supplies a fixed literal APPLICATION_ID of 7000 (Purchasing) and an EVENT_TYPE of 'PO RECEIPT', which identifies the record class to downstream consumers such as the Asia/Pacific Accounting Events (AEL) drill-down framework.

Key to its behavior is the use of explicit Oracle hints, most notably ORDERED and a series of USE_NL directives. This is the object that matches the user's search for "use_nl": the view text is heavily tuned to force nested-loop joins across the chain of JA receipt tables and GL tables, guaranteeing predictable execution plans on large receipt datasets. An accompanying INDEX(GJR GL_IMPORT_REFERENCES_N3) hint steers access to the GL import references table.

Underlying Base Objects

The view is defined over a broad set of documented base objects spanning Purchasing, Receiving, General Ledger, and HR:

• JA receipt tables: JAI_RCV_HEADERS, JAI_RCV_LINES, JAI_RCV_TRANSACTIONS, JAI_RCV_RGM_LINES, JAI_RGM_TRX_RECORDS — the localization receipt header, line, transaction, and RGM structures that drive the nested-loop joins.
• Receiving: RCV_SHIPMENT_LINES supplies RECEIPT_LINE_NUMBER and subinventory.
• General Ledger: GL_JE_HEADERS, GL_JE_LINES, GL_JE_CATEGORIES, GL_JE_SOURCES, GL_DAILY_CONVERSION_TYPES, and GL_IMPORT_REFERENCES.
• Inventory / Suppliers: MTL_MATERIAL_TRANSACTIONS, MTL_UNITS_OF_MEASURE_VL, PO_VENDORS, PO_VENDOR_SITES_ALL.
• Utilities: FND_GLOBAL, HR_GENERAL, HR_LOCATIONS, and JAI_DRILLDOWN_UTILS_PKG.

The USE_NL hints bind the driving chain JRH→JRL→JRT→RSL/JRRL→GJR→GJH→GJL/GJS/GJC/GLCT, forcing the optimizer to descend this hierarchy rather than hash-joining, which preserves row-level accounting linkage.

Key Columns

• Reconciliation keys: TRX_HDR_ID (SHIPMENT_HEADER_ID), TRX_HDR_TABLE ('JAI_RCV_TRANSACTIONS'), JE_HEADER_ID, JE_LINE_NUM.
• Accounting amounts: ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR, ACCOUNT_ID (CODE_COMBINATION_ID), ACCOUNTING_DATE.
• Currency data: ENTERED_CURRENCY, CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_TYPE, CURRENCY_USER_CONVERSION_TYPE.
• GL classification: JE_SOURCE, USER_JE_SOURCE_NAME, JE_CATEGORY, USER_JE_CATEGORY_NAME, LEDGER_ID (SET_OF_BOOKS_ID).
• Item / receipt detail: TRX_TYPE, RECEIPT_NUM, RECEIPT_LINE_NUMBER, ITEM_ID, QUANTITY, SUB_INVENTORY, INVENTORY_ORG_ID.
• Localization tax fields: EXCISE_INV_NUM, EXCISE_INVOICE_DATE, VAT_INV_NUM, VAT_INVOICE_DATE (derived via NVL across header/line sources).

Common Use Cases and Queries

Typical scenarios include AEL drill-down reporting, reconciling PO receipts to GL journals, and auditing localization excise/VAT capture on receipts.

SELECT trx_hdr_id, receipt_num, je_header_id, je_line_num,
       entered_dr, entered_cred
FROM   apps.jai_po_rec_ael_v
WHERE  trx_hdr_id = :p_shipment_header_id;

SELECT je_header_id, je_line_num, user_je_category_name,
       accounted_dr, accounted_credit
FROM   apps.jai_po_rec_ael_v
WHERE  je_source = 'Purchasing'
AND    accounting_date BETWEEN :p_from AND :p_to;

Because the view hard-codes the ORDERED and USE_NL hints, application of additional filters on the driving JA receipt columns is strongly recommended to keep execution efficient; filter predicates should target TRX_HDR_ID, RECEIPT_NUM, or ACCOUNTING_DATE to preserve the intended nested-loop access path.