Search Results pay_jp_bal_matrix_by_date_v




Overview
Explore in AI Chat

Overview

PAY_JP_BAL_MATRIX_BY_DATE_V is a database view owned by the APPS schema in Oracle E-Business Suite, defined in the PAY (Payroll) product family. According to ETRM documentation, it is a balance view whose purpose is restricted to the Japanese localization. It exposes accrued payroll balance values for an assignment as of a supplied effective date, expressed as a matrix of balance rows across many standard payroll time-bucket dimensions (run, period-to-date, month-to-date, year-to-date, fiscal-year-to-date, August-to-July, inception-to-date, July-to-June, and similar). The view does not store data itself; it derives each value at runtime by calling the PAY_JP_BALANCE_VIEW_PKG.GET_VALUE API for every balance type returned by the driving query.

The view therefore functions as a reporting and integration layer rather than a transactional object. It is typically consumed by Japanese localization reports, balance inquiries, and downstream extracts that need a compact, date-driven grid of balance amounts per assignment without repeatedly invoking the balance retrieval package directly. Because all values are computed through the package, the view reflects current run results and respects the reset and effective-date semantics encoded in each dimension key.

Underlying Base Objects

The documented referenced base objects are FND_SESSIONS, HR_GENERAL, PAY_ASSIGNMENT_ACTIONS, PAY_BALANCE_FEEDS_F, PAY_BALANCE_TYPES, PAY_BALANCE_TYPES_TL, PAY_JP_BALANCE_VIEW_PKG, PAY_PAYROLL_ACTIONS, PAY_RUN_RESULTS, PAY_RUN_RESULT_VALUES, and the view's own package dependency PAY_JP_BALANCE_VIEW_PKG.

The driving query joins these sources to produce one row per assignment and balance type. Payroll actions, assignment actions, run results, and run result values supply the assignment context and the effective date that is passed to the package. PAY_BALANCE_FEEDS_F, PAY_BALANCE_TYPES, and PAY_BALANCE_TYPES_TL identify the balances to be evaluated and their translated names. HR_GENERAL provides the decode of the unit of measure for display, and FND_SESSIONS supports session context resolution. The view text is annotated with the hint ORDERED USE_NL(PAA PPA PRR PRRV FEED PBT PBTTL), indicating that the optimizer should drive from the assignment action aliases (PAA, PPA) and resolve balance definitions through nested loops.

Key Columns

Common Use Cases and Queries

Typical uses include Japanese localization balance reporting, reconciliation of month-to-date and year-to-date accumulations for an assignment, and provision of a single-row balance matrix for payroll extracts or interfaces. Because balance retrieval is delegated to PAY_JP_BALANCE_VIEW_PKG.GET_VALUE, queries should filter on ASSIGNMENT_ID to limit the number of package invocations, as each additional assignment multiplies the cost of the nested-loop execution plan.

A representative query retrieves the principal Japanese time-bucket balances for a single assignment:

  • SELECT assignment_id, balance_name, asg_run_bal, asg_mtd_jp_bal, asg_ytd_jp_bal, asg_aug2jul_jp_bal, asg_jul2jun_jp_bal FROM apps.pay_jp_bal_matrix_by_date_v WHERE assignment_id = :p_assignment_id;

Where the effective date is fixed by the driving join, results reflect the session and run context resolved through FND_SESSIONS and the payroll action records, ensuring the Japanese reset semantics defined in each dimension key are honoured.