Search Results payment_method




Overview
Explore in AI Chat

Overview

PAY_NZ_ASG_PAYMENTS_V is a New Zealand localisation view owned by the APPS schema within the Oracle Payroll (PAY) product family. It presents assignment-level payment information for payroll actions whose action type is either 'P' (payroll run) or 'U' (quick pay / unscheduled run). The view is designed to expose, in a single denormalised result set, the payment method, bank account details, and payment amount associated with each assignment action processed by a payroll action.

The view returns one row per assignment action, identified by ASSIGNMENT_ACTION_ID and ROW_ID. Because it is a UNION of two queries, it addresses both the standard case, where a personal payment method linked to an external account is present, and the fallback case, where a payment is made using only an organisation payment method with no personal payment method. In the second branch, bank-related columns return NULL. This design makes the view suitable for payroll reconciliation, third-party disbursement reporting, and New Zealand bank payment interfaces that require bank account segmentation and reference data.

Underlying Base Objects

The view is defined over the following documented base objects:

The external account join to the personal payment method is outer (+), and the HR_LOOKUPS join is likewise outer, allowing rows to survive missing bank lookup definitions.

Key Columns

  • ROW_ID — the ROWID of the underlying PAY_ASSIGNMENT_ACTIONS row.
  • ASSIGNMENT_ACTION_ID — unique identifier of the assignment action; the primary join key for downstream reporting.
  • PAYMENT_METHOD — org payment method name. In the first UNION branch this is the full name; in the second branch it is truncated to 30 characters via SUBSTR.
  • BANK_ACCOUNT — concatenation of SEGMENT1-SEGMENT2-SEGMENT3 from PAY_EXTERNAL_ACCOUNTS, representing the full bank account number.
  • CODE — SEGMENT4 of the external account, typically a bank or branch code.
  • REFERENCE — SEGMENT5 of the external account, typically the payment reference or particulars.
  • PAYMENT_AMOUNT — the prepayment value (PP.VALUE) for the assignment action.
  • BANK — the HR_LOOKUPS.MEANING derived from the first two characters of SEGMENT1 against lookup type NZ_BANK; NULL in the fallback branch.

Common Use Cases and Queries

Typical usage includes New Zealand bank file preparation, payroll disbursement reconciliation, and auditing of payment methods by assignment.

To retrieve all payments for a given payroll action:

  • SELECT assignment_action_id, payment_method, bank_account, code, reference, payment_amount FROM apps.pay_nz_asg_payments_v WHERE assignment_action_id = :assignment_action_id;

To summarise payments by method:

  • SELECT payment_method, COUNT(*), SUM(payment_amount) FROM apps.pay_nz_asg_payments_v GROUP BY payment_method;

To isolate records that have bank details, filter on bank_account IS NOT NULL, which selects only the first UNION branch and effectively excludes the fallback rows.