Search Results stat_amount

Overview

PSB_AE_LINES is a reporting and integration view in the Public Sector Budgeting (PSB) module of Oracle E-Business Suite, applicable to releases 12.1.1 and 12.2.2. It exposes the accounting entry line information used to move budgeting transactions from the PSB subledger into the Oracle General Ledger interface. The view is the secure, operating-unit-filtered representation of the underlying accounting entry lines and is the object most commonly referenced in PSB-to-GL reconciliation, extract programs, and custom reports.

The view is not a physical table and stores no data of its own. It is defined as a query over PSB_AE_LINES_ALL, returning the current row set for the querying session based on the operating unit recorded in the client environment. Per the ETRM metadata, the object is marked “Not implemented in this database” when the PSB product is not installed or the underlying table does not exist, so dependencies should be confirmed before use.

Underlying Base Objects

The only documented base object is PSB_AE_LINES_ALL. The view applies the standard Oracle multi-organization security predicate, resolving the operating unit from USERENV('CLIENT_INFO') and matching it against ORG_ID. Rows whose ORG_ID cannot be resolved are compared against the sentinel value -99, effectively excluding them from the result set. No other base objects are documented in the ETRM metadata, and the view does not itself join to headers or code combinations; those relationships are established through the AE_HEADER_ID and CODE_COMBINATION_ID keys rather than through the view definition.

Key Columns

• AE_LINE_ID – Primary key of the accounting entry line, and the column most frequently used as a search term and join key.
• AE_HEADER_ID – Foreign key to the accounting entry header that owns the line.
• AE_LINE_NUMBER – Sequence number of the line within the header.
• AE_LINE_TYPE_CODE – Classification of the line within the accounting entry structure.
• CODE_COMBINATION_ID – The accounting flexfield combination to which the amount is posted.
• ACTUAL_FLAG – Identifies whether the line represents actual, budget, or encumbrance balances.
• CURRENCY_CODE, CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_RATE – Currency and conversion attributes for the entry.
• ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR – Entered and accounted debit and credit amounts in the functional currency.
• SOURCE_TABLE, SOURCE_ID – The originating document that generated the accounting entry.
• GL_SL_LINK_ID – Link to the General Ledger subledger accounting relationship.
• ACCOUNTING_ERROR_CODE, GL_TRANSFER_ERROR_CODE – Error codes returned when the line fails validation or transfer to General Ledger.
• STAT_AMOUNT – Statistical amount carried on the line.
• USSGL_TRANSACTION_CODE – Treasury and USSGL classification code used in federal accounting.
• SUBLEDGER_DOC_SEQUENCE_ID, SUBLEDGER_DOC_SEQUENCE_VALUE – Subledger document sequencing identifiers.
• ORG_ID – Operating unit that owns the line and drives the view's security filter.
• REFERENCE1 through REFERENCE10 – Descriptive and contextual reference attributes carried from the source transaction.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_UPDATE_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, REQUEST_ID – Standard audit, concurrency, and concurrent program context columns.

Common Use Cases and Queries

PSB administrators use the view to verify that budget accounting entries have been generated correctly, to identify entries that failed to transfer to General Ledger, and to reconcile PSB balances against GL balances by code combination and period. It is also used to trace a specific line back to its originating document via SOURCE_TABLE and SOURCE_ID.

Retrieve a line by its primary key:

• SELECT ae_line_id, ae_header_id, ae_line_number, code_combination_id, accounted_dr, accounted_cr FROM psb_ae_lines WHERE ae_line_id = :p_line_id;

Locate lines with transfer or accounting errors:

• SELECT ae_line_id, ae_header_id, accounting_error_code, gl_transfer_error_code FROM psb_ae_lines WHERE gl_transfer_error_code IS NOT NULL OR accounting_error_code IS NOT NULL;

Reconcile debits and credits for an operating unit:

• SELECT code_combination_id, currency_code, SUM(accounted_dr), SUM(accounted_cr) FROM psb_ae_lines WHERE org_id = :p_org_id GROUP BY code_combination_id, currency_code;

Because the view enforces operating unit security through USERENV, sessions that do not initialize the client environment correctly may return no rows. When full cross-operating-unit visibility is required, query PSB_AE_LINES_ALL directly with appropriately restricted privileges.