Search Results total_lines




Overview
Explore in AI Chat

Overview

JAI_RCV_RGM_HEADERS_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Asia/Pacific Localizations product (JA). It belongs to the Receiving Goods Management (RGM) functionality used by Asia/Pacific localizations, where it supports the reconciliation of recoverable and recovered tax amounts associated with received goods. The view presents receipt-level aggregated tax data by grouping line-level detail into a header-level summary keyed on organization, location, receipt number, and shipment header.

In Oracle EBS 12.1.1 and 12.2.2, the view serves as a convenient reporting and integration layer for users and external processes that require summarized recoverable tax information without traversing the line-level detail. Because the view is defined with a GROUP BY clause, it exposes one consolidated row per receipt — identified primarily by RECEIPT_NUM — along with cumulative monetary totals and distinct counts of the invoices, invoice dates, vendors, and vendor sites contributing to that receipt. This structure makes it particularly valuable for reconciliation reports, upstream/downstream data extracts, and queries driven by the receipt number search term.

Underlying Base Objects

The view is defined exclusively over the documented base object JAI_RCV_RGM_LINES, accessed through a synonym in the APPS schema. JAI_RCV_RGM_LINES stores the granular, line-level tax and invoice details captured when receipts are processed under the RGM localization flow. The view applies aggregate functions on this base object: SUM over RECOVERABLE_AMOUNT and RECOVERED_AMOUNT, and COUNT(DISTINCT ...) and COUNT(*) operations across invoice, vendor, and line dimensions.

The documented view text groups the underlying rows by ORGANIZATION_ID, LOCATION_ID, RECEIPT_NUM, and SHIPMENT_HEADER_ID, producing exactly one summary row per unique combination of these four attributes. Columns such as CNT_INVOICE_NO, CNT_INVOICE_DATE, CNT_VENDOR_ID, and CNT_VENDOR_SITE_ID are derived using COUNT(DISTINCT ...), while LINES_WO_VAT_DETAILS is computed by summing a DECODE that counts lines where INVOICE_NO is null (that is, lines lacking VAT detail). The NVL wrappers ensure that receipts with no recoverable or recovered amounts report zero rather than null.

Key Columns

  • ORGANIZATION_ID — The inventory organization under which the receipt was recorded; part of the grouping key.
  • LOCATION_ID — The location associated with the receipt; part of the grouping key.
  • RECEIPT_NUM — The receipt number. Because the user searched on this term, it is the principal business identifier for retrieving a consolidated RGM header row.
  • SHIPMENT_HEADER_ID — The shipment header linking the receipt to its receiving shipment; completes the grouping key.
  • RECOVERABLE_AMOUNT — Sum of recoverable tax amounts across qualifying lines; defaults to 0 when null.
  • RECOVERED_AMOUNT — Sum of recovered tax amounts across qualifying lines; defaults to 0 when null.
  • CNT_INVOICE_NO / CNT_INVOICE_DATE — Distinct counts of invoice numbers and invoice dates contributing to the receipt.
  • CNT_VENDOR_ID / CNT_VENDOR_SITE_ID — Distinct counts of vendors and vendor sites associated with the receipt lines.
  • TOTAL_LINES — Total number of line records aggregated for the receipt.
  • LINES_WO_VAT_DETAILS — Count of lines that carry no invoice (VAT) detail, highlighting incomplete tax documentation.

Common Use Cases and Queries

Typical usage includes receipt-level recoverable tax reporting, variance review between recoverable and recovered amounts, and identifying receipts with missing VAT detail. A frequent query pattern searches directly on the receipt number:

  • Retrieve details for a specific receipt:
    SELECT receipt_num, organization_id, recoverable_amount, recovered_amount, total_lines, lines_wo_vat_details
    FROM apps.jai_rcv_rgm_headers_v
    WHERE receipt_num = :p_receipt_num;
  • List receipts with unrecovered tax:
    SELECT receipt_num, recoverable_amount, recovered_amount
    FROM apps.jai_rcv_rgm_headers_v
    WHERE recoverable_amount > recovered_amount;
  • Identify receipts lacking VAT detail:
    SELECT receipt_num, total_lines, lines_wo_vat_details
    FROM apps.jai_rcv_rgm_headers_v
    WHERE lines_wo_vat_details > 0;

Because the view aggregates multiple underlying lines, it is well suited to summary reporting and integration extracts keyed on receipt number, while detailed line-level inspection should be performed against JAI_RCV_RGM_LINES directly.