---
title: "SQL-065: clsPaymentReturnCollection retrieve"
summary: Retrieves payment return records from PAYMENT_OWNER.PAYMENT_RETURN with culture-aware reason code descriptions and joined detail and authorization data. WHERE clause is built dynamically by the collection criteria.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business.Payment.EFT
reference_type: package
---

# SQL-065: clsPaymentReturnCollection retrieve

Retrieves payment return records from PAYMENT_OWNER.PAYMENT_RETURN with culture-aware reason code descriptions and joined detail and authorization data. WHERE clause is built dynamically by the collection criteria.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-065 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle* |
| **Schema** | `PAYMENT_OWNER` (primary tables) |
| **Command type** | Text |
| **Business object** | `clsPaymentReturnCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve / AfterRetrieve` |


## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| \:CultureCode\ | Input | String | 5 | Binds current thread culture two-letter ISO language name* |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PAYMENT_OWNER.PAYMENT_RETURN](../database-tables.md#payment_ownerpayment_return) | Read | All columns |
| [PAYMENT_OWNER.PAYMENT_RETURN_REASON_CODE](../database-tables.md#payment_ownerpayment_return_reason_code) | Read (join) | CODE, CODE_DESCR, RETURN_PAYMENT_EVENT_CODE |
| [PAYMENT_RETURN_REASON_GCODE](../database-tables.md#payment_return_reason_gcode) | Read (left join) | CODE, CODE_DESCR, CULTURE_CODE |
| [PAYMENT_OWNER.PAYMENT_DETAIL](../database-tables.md#payment_ownerpayment_detail) | Read (left join) | PAYMENT_DETAIL_SEQ_NUM, CONFIRMATION_CHECK_NUM, NSF_COUNT |
| [PAYMENT_OWNER.PAYMENT_AUTHORIZATION](../database-tables.md#payment_ownerpayment_authorization) | Read (left join) | PAYMENT_AUTHORIZATION_SEQ_NUM, CUSTOMER_TYPE_CODE, contact columns, AUTHORIZATION_TYPE_CODE |
| [PAYMENT_OWNER.AUTHORIZATION_TYPE_CODE](../database-tables.md#payment_ownerauthorization_type_code) | Read (left join) | CODE, CODE_DESCR |

## Sample SQL

```sql
Select PR.CREATE_DATE as CreateDate,
  PR.CREATE_USER as CreateUser,
  PR.CREDIT_DEBIT_CODE as CreditDebitCode,
  PR.CUSTOMER_ID as CustomerID,
  PR.CUSTOMER_NAME as CustomerName,
  PR.PAYMENT_AMT as PaymentAmt,
  PR.PAYMENT_DETAIL_SEQ_NUM as PaymentDetailSeqNum,
  PR.PAYMENT_RETURN_REASON_CODE as PaymentReturnReasonCode,
  case when prrcG.code_descr is null
    then cast(prrc.CODE_DESCR as nvarchar2(2000))
    else prrcG.code_descr end AS PaymentReturnReasonCodeDescr,
  PRRC.RETURN_PAYMENT_EVENT_CODE as ReturnPaymentEventCode,
  PR.PAYMENT_RETURN_SEQ_NUM as PaymentReturnSeqNum,
  PR.PAYMENT_SOURCE_SYSTEM_CODE as PaymentSourceSystemCode,
  PR.RETURN_ADDENDA_LINE as ReturnAddendaLine,
  PR.RETURN_DATE as ReturnDate,
  PR.RETURN_DETAIL_LINE as ReturnDetailLine,
  PR.RETURN_STATUS_CODE as ReturnStatusCode,
  PR.RETURN_TYPE_CODE as ReturnTypeCode,
  PR.TRACE_NUM as TraceNum,
  PR.UPDATE_DATE as UpdateDate,
  PR.UPDATE_USER as UpdateUser,
  PD.CONFIRMATION_CHECK_NUM AS ConfirmationCheckNum,
  PD.NSF_COUNT as TransactionNSFCount,
  PA.CUSTOMER_TYPE_CODE as CustomerTypeCode,
  PA.CUSTOMER_CONTACT_NAME as CustomerContactName,
  PA.CUSTOMER_CONTACT_PHONE_NUM as CustomerContactPhoneNumber,
  PA.CUSTOMER_CONTACT_EMAIL_ADDR as CustomerContactEmail,
  PA.AUTHORIZATION_TYPE_CODE as AuthorizationTypeCode,
  ATC.CODE_DESCR as AuthorizationPaymentFrequency,
  ATC.CODE_DESCR as AuthorizationTypeDescription
From payment_owner.PAYMENT_RETURN PR
  JOIN payment_owner.payment_return_reason_code prrc
    ON (pr.payment_return_reason_code = prrc.code)
  left outer join PAYMENT_RETURN_REASON_GCODE prrcG
    on (prrcG.code = prrc.code
    and upper(prrcG.CULTURE_CODE) = upper(:CultureCode))
  LEFT OUTER JOIN payment_owner.payment_detail pd
    ON (pr.payment_detail_seq_num = pd.payment_detail_seq_num)
  LEFT OUTER JOIN payment_owner.payment_authorization pa
    ON (pd.payment_authorization_seq_num = pa.payment_authorization_seq_num)
  LEFT OUTER JOIN payment_owner.authorization_type_code atc
    ON (pa.authorization_type_code = atc.code)
-- WHERE predicates appended by collection criteria*
```

## Operations and processes
- [Payment return handling](../operations/payment-return-handling.md)*

> **Note:** Statements marked with `*` were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Business rules
- [BR-EFT-03: Payment return classified by NACHA reason code](../business-rules.md#br-eft-03-returned-eft-payment-classified-by-nacha-return-reason-code)
- [BR-EFT-07: Return reason descriptions are culture-aware](../business-rules.md#br-eft-07-return-reason-description-is-culture-aware)

## Sources
- `Business\SAIF.Business.Payment.EFT\clsPaymentReturnCollection.vb`