Search Results jv_quantity




Overview
Explore in AI Chat

Overview

APPS.PMITS_TEST_SUBLEDGER_V is a reporting view in the Process Manufacturing (OPM) subledger schema of Oracle E-Business Suite, exposed under the APPS account and associated with the Process Manufacturing Intelligence/Transfer (PMITS) component. It projects a consolidated subledger transaction image — combining financial accounting attributes with inventory and process-manufacturing transaction detail — into a single relational interface that can be queried for reconciliation, audit, and downstream integration purposes. Because it resides in APPS and is defined over an already-materialized subledger test view (GL_SUBR_TST_VW), it is intended as a presentation/tuning layer rather than a base transactional table. In 12.1.1 and 12.2.2 the view behaves identically from a SQL perspective, though 12.2.2 online patching and editioning considerations mean the underlying view and package references should be validated after each patch cycle.

Underlying Base Objects

The ETRM metadata identifies exactly two referenced objects: the view GL_SUBR_TST_VW and the package PMI_SECURITY_PKG. All column projections are drawn from GL_SUBR_TST_VW; the package is referenced only inside the WHERE predicate. The row filter applies organization-level security through PMI_SECURITY_PKG.SHOW_RECORD(ORGN_CODE), so only organizations the current user is authorized to see are returned, while rows with a NULL ORGN_CODE bypass the security check. This makes the view subject to the same MOAC/organization security profile as the OPM subledger, which is important when the view is consumed from concurrent programs or BI Publisher data models that run under a specific responsibility.

Key Columns

Columns fall into four functional groups. Accounting-ledger columns include SOURCE, SUBLEDGER_ID, CO_CODE, FISCAL_YEAR, PERIOD, SUB_EVENT_CODE, VOUCHER_ID, DEBIT_CREDIT_SIGN, AMOUNT_BASE, AMOUNT_TRANS, CURRENCY_BASE, and CURRENCY_TRANS. Document-identity columns include DOC_TYPE, DOC_ID, JOIN_DOC_ID (derived via DECODE over XFER/TRNI/MTRI/TRNR document types, defaulting to 1), LINE_ID, DOC_NO, DOC_DATE, and LINE_NO. Inventory and material detail is carried by RESOURCE_ITEM_NO, RESOURCE_ITEM_NO_DESC, TRANS_DATE, WHSE_CODE, TRANS_QTY_USAGE, and TRANS_QTY_USAGE_UM. The quantities relevant to the user's search term are JV_QUANTITY and JV_QUANTITY_UM — the journal-voucher quantity and its unit of measure, exposed alongside TRANS_QTY_USAGE_UM so that the accounting quantity and the physical usage quantity/unit can be compared within one row. A derived classification column uses DECODE on SUB_EVENT_CODE to label STEP, CLOS, and RVAL events as 'NONMATERIAL' and all others as 'MATERIAL'.

Common Use Cases and Queries

Typical uses include subledger-to-GL reconciliation, material versus non-material event analysis, and quantity/unit-of-measure variance reporting where JV_QUANTITY_UM differs from TRANS_QTY_USAGE_UM.

  • Quantity/UOM reconciliation: SELECT DOC_NO, RESOURCE_ITEM_NO, JV_QUANTITY, JV_QUANTITY_UM, TRANS_QTY_USAGE, TRANS_QTY_USAGE_UM FROM APPS.PMITS_TEST_SUBLEDGER_V WHERE JV_QUANTITY_UM <> TRANS_QTY_USAGE_UM;
  • Period ledger extract: SELECT SUBLEDGER_ID, ACCT_NO, AMOUNT_BASE, CURRENCY_BASE FROM APPS.PMITS_TEST_SUBLEDGER_V WHERE FISCAL_YEAR = :yr AND PERIOD = :pd AND CO_CODE = :co;
  • Document drill-down by type: SELECT DOC_TYPE, DOC_ID, LINE_ID, TRANS_DATE FROM APPS.PMITS_TEST_SUBLEDGER_V WHERE DOC_TYPE = 'XFER' AND DOC_NO = :docno;
  • Material/non-material split: SELECT SUB_EVENT_CODE, DECODE(SUB_EVENT_CODE,'STEP','NONMATERIAL','CLOS','NONMATERIAL','RVAL','NONMATERIAL','MATERIAL') CLASS, SUM(AMOUNT_BASE) FROM APPS.PMITS_TEST_SUBLEDGER_V GROUP BY SUB_EVENT_CODE;

Because organization security is enforced inside the view, queries run under a responsibility with restricted organization access will silently return a reduced row set; test data models should therefore be validated against a responsibility with full organization visibility.