Search Results order_category

Overview

The view ASO_I_INVOICES_V is an Oracle E-Business Suite (EBS) database object owned by the APPS schema and validated in releases 12.1.1 and 12.2.2. It belongs to the ASO – Order Capture product family and, per the ETRM metadata, is described as a "View related to Invoice Information in OC." In practice, the view presents a consolidated, inquiry-oriented projection that links order capture header and line data to the corresponding accounts receivable invoice transactions. It exposes both order-side attributes (order number, order category, order type, sold-to customer, ordered date, ship method) and invoice-side attributes (invoice number, invoice date, bill-to customer), allowing consumers to traverse from a sales order directly to its billing outcome.

Because it is a view rather than a table, ASO_I_INVOICES_V is non-updatable and is intended strictly for read access. Its role is to support Oracle EBS reporting, order-to-invoice reconciliation, and integration queries where a single relational source is required to join order capture with receivables billing information. It is commonly referenced by custom reports, extracts, and interface programs that need invoice visibility from within the Order Capture context.

Underlying Base Objects

The view is defined over a documented set of base objects, combining order management, receivables, and ASO inquiry views:

• OE_ORDER_HEADERS_ALL (via synonym OE_ORDER_HEADERS) – order header attributes such as order number, category code, type, customer, and dates.
• OE_ORDER_LINES_ALL (via synonym OE_ORDER_LINES) – order line identifiers and the credit invoice line linkage.
• OE_LOOKUPS (view) – resolves the order category code to its descriptive meaning.
• RA_CUSTOMER_TRX_ALL (synonym) – accounts receivable transaction header, providing invoice number and date.
• RA_CUSTOMER_TRX_LINES_ALL (synonym) – receivables transaction lines used to tie orders to invoices.
• ASO_I_ORDER_TYPES_V (view) – order type name and ship method.
• ASO_I_CUST_ACCOUNTS_V (view) – customer name and account number.

The join between order lines and invoice lines is performed in two ways: either through CREDIT_INVOICE_LINE_ID matching CUSTOMER_TRX_LINE_ID, or through a decode of INTERFACE_LINE_ATTRIBUTE6 when the leading digits are stripped and a numeric value remains. A DISTINCT operator eliminates duplicates arising from these overlapping join paths.

Key Columns

• INVOICE_NUMBER / INVOICE_DATE – the receivables transaction number (TRX_NUMBER) and transaction date.
• ORDER_NUMBER / HEADER_ID – sales order number and its unique header identifier.
• ORDER_CATEGORY / ORDER_CATEGORY_DESC – the raw ORDER_CATEGORY_CODE and its lookup meaning (lookup type ORDER_CATEGORY). This is the column most directly associated with the search term order_category.
• ORDER_TYPE / ORDER_TYPE_ID – descriptive order type name and its identifier.
• PURCHASE_ORDER_NUM – the customer purchase order reference (CUST_PO_NUMBER).
• CUSTOMER_NAME, CUSTOMER_ID, CUSTOMER_NUMBER – sold-to customer name and account identifiers.
• BILL_TO_CUSTOMER_ID – the receivables bill-to customer identifier.
• DATE_ORDERED, SHIP_METHOD_CODE – order placement date and shipping method.
• ORDER_LINE_ID – the order line identifier (LINE_ID).

Common Use Cases and Queries

Typical scenarios include reconciling sales orders to invoices, reporting order-to-cash status by order category, and validating that order lines have been billed. Because order_category is a documented column, filtering or grouping on it is a frequent pattern.

Sample query returning invoices with their order category description:

 SELECT invoice_number, invoice_date, order_number,
        order_category, order_category_desc, order_type,
        customer_name, date_ordered, order_line_id
 FROM   apps.aso_i_invoices_v
 WHERE  order_category_desc = 'Order'
 ORDER BY invoice_date DESC; 

Sample query joining invoice detail to an order category lookup summary:

 SELECT order_category, COUNT(DISTINCT invoice_number) invoice_count,
        SUM(1) line_count
 FROM   apps.aso_i_invoices_v
 GROUP  BY order_category; 

Both examples rely solely on view columns documented in the ETRM metadata. Consumers should note the view returns line-level granularity due to ORDER_LINE_ID; aggregation is therefore recommended when order- or invoice-level counts are required.