Search Results pmits_test_subledger_v




Overview
Explore in AI Chat

Overview

PMITS_TEST_SUBLEDGER_V is an APPS-owned database view within the Oracle E-Business Suite environment, belonging to the PMI (Process Manufacturing Intelligence) product family, which is documented as obsolete in later releases. The view is reported with a VALID status and functions as a General Ledger subsidiary ledger construct — a liaison between OPM (Oracle Process Manufacturing) subsystems and the General Ledger. It presents all bookings of OPM subsystem transactions (documents), which are populated and maintained by the OPM Subsidiary Ledger Update process. In the context of EBS 12.1.1 and 12.2.2, this view offers a flattened, enriched projection over a base subsidiary ledger view, permitting reporting, reconciliation, and downstream integration of OPM transaction lines with their corresponding financial and inventory attributes.

Underlying Base Objects

The documented metadata identifies two referenced base objects for this view. The first is GL_SUBR_TST_VW, a VIEW from which the majority of columns are selected; the main query selects its columns and applies decode logic and a security-based filter predicate. The second is PMI_SECURITY_PKG, a PACKAGE used in the WHERE clause to enforce organization-level security. Specifically, the predicate (ORGN_CODE IS NULL OR 'TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(ORGN_CODE)) restricts returned rows to organizations the current user is authorized to view. The view therefore acts as a secured, presentation-layer projection over the subsidiary ledger view rather than querying base tables directly. Two derived columns are added through DECODE expressions: JOIN_DOC_ID and TRANS_TYPE.

Key Columns

The view exposes financial and inventory-related columns relevant to subsidiary ledger reporting. AMOUNT_BASE and AMOUNT_TRANS store the transaction amount in functional (base) and transaction currencies respectively, while CURRENCY_BASE and CURRENCY_TRANS identify those currencies. DEBIT_CREDIT_SIGN indicates the accounting side. ACCT_NO, ACCT_TTL_CODE, and ACCT_DESC identify the account, its title code, and description. SUB_EVENT_CODE and SUB_EVENT_DESC describe the subsidiary event; VOUCHER_ID, DOC_ID, DOC_NO, DOC_DATE, and LINE_ID identify the source document and line. JOIN_DOC_ID is derived via DECODE on DOC_TYPE — returning DOC_ID for types 'XFER', 'TRNI', 'MTRI', and 'TRNR', and a literal value of 1 otherwise. TRANS_TYPE is derived similarly: SUB_EVENT_CODE values 'STEP', 'CLOS', and 'RVAL' map to 'NONMATERIAL', with all others mapping to 'MATERIAL'. Additional columns include ORGN_CODE, WHSE_CODE, RESOURCE_ITEM_NO, TRANS_QTY_USAGE, JV_QUANTITY, and GL_TRANS_DATE.

Common Use Cases and Queries

This view is commonly used to report OPM transaction activity flowing into GL, to reconcile amounts between the base and transaction currencies, and to audit material versus non-material events. Because the user's search term was "amount_trans", the most direct use is filtering or projecting transaction-currency amounts. For example:

  • SELECT DOC_NO, SUB_EVENT_CODE, TRANS_TYPE, AMOUNT_TRANS, CURRENCY_TRANS FROM PMITS_TEST_SUBLEDGER_V WHERE AMOUNT_TRANS > 0;
  • SELECT ORGN_CODE, PERIOD, SUM(AMOUNT_TRANS) FROM PMITS_TEST_SUBLEDGER_V GROUP BY ORGN_CODE, PERIOD;
  • SELECT DOC_ID, JOIN_DOC_ID, DOC_TYPE, TRANS_TYPE FROM PMITS_TEST_SUBLEDGER_V WHERE DOC_TYPE IN ('XFER','TRNI','MTRI','TRNR');

These queries illustrate reporting on transaction amounts by period and organization, verifying the derived JOIN_DOC_ID and TRANS_TYPE logic, and validating that security predicates are applied implicitly through PMI_SECURITY_PKG.