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	Read	All columns
PAYMENT_OWNER.PAYMENT_RETURN_REASON_CODE	Read (join)	CODE, CODE_DESCR, RETURN_PAYMENT_EVENT_CODE
PAYMENT_RETURN_REASON_GCODE	Read (left join)	CODE, CODE_DESCR, CULTURE_CODE
PAYMENT_OWNER.PAYMENT_DETAIL	Read (left join)	PAYMENT_DETAIL_SEQ_NUM, CONFIRMATION_CHECK_NUM, NSF_COUNT
PAYMENT_OWNER.PAYMENT_AUTHORIZATION	Read (left join)	PAYMENT_AUTHORIZATION_SEQ_NUM, CUSTOMER_TYPE_CODE, contact columns, AUTHORIZATION_TYPE_CODE
PAYMENT_OWNER.AUTHORIZATION_TYPE_CODE	Read (left join)	CODE, CODE_DESCR

Sample 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*

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
• BR-EFT-07: Return reason descriptions are culture-aware

Sources

• Business\SAIF.Business.Payment.EFT\clsPaymentReturnCollection.vb