`paypal` Database Table Usage and Changes

paypal Table Usage and Changes

Field Name	Usage
paypal_ipn_id	n/c
order_id	n/c
txn_type	Set to identify the content-type for the record, one of CREATE, AUTHORIZE, CAPTURE, REFUND or VOID.
final_capture	For captured funds, indicates whether/not this is a final_capture. Added by this payment module
module_name	Set to 'paypalr'
module_mode	Set only when txn_mode is CREATE, identifies the mode in which the original order was created; one of AUTHORIZE or CAPTURE.
reason_code	Currently unused
payment_type	The type of PayPal payment, either paypal or card.
payment_status	The['status'] value returned by PayPal for the associated request.
pending_reason	Set to indicate the pending-reason if the payment_status is PENDING. The size of this field was updated to varchar(64) by this payment module, was varchar(32).
invoice1	See the note below for details; the value will be similar to PPR-20231223171714-7-JohDoe-e32d2ed2.
mc_currency	Set to the PayPal order's['amount']['currency_code']
first_name1	Set to the PayPal order's['payment_source']['name']['given_name'] for 'paypal' orders. For 'card' orders, the portion of the card-holder's name prior to the first blank.
last_name1	Set to the PayPal order's ['payment_source']['name']['surname'] for 'paypal' orders. For 'card' orders, the portion of the card-holder's name after the first blank.
payer_business_name	Currently unused
address_name1, 2	['shipping']['name']['full_name']
address_street1, 2	['shipping']['address']['address_line_1'], with ['shipping']['address']['address_line_2'], if provided.
address_city1, 2	['shipping']['address']['admin_area_2']
address_state1, 2	['shipping']['address']['admin_area_1']
address_zip1, 2	['shipping']['address']['postal_code']
address_country1, 2	['shipping']['address']['country_code']
address_status	Not used; not supplied by PayPal.
payer_email3	The PayPal Wallet payer's email address (['payer']['email_address']).
payer_id3	The PayPal Wallet payer's PayPal ID (['payer']['payer_id']).
payer_status3	The PayPal Wallet payer's PayPal account status (['payment_source'][$type]['account_status']).
payment_date4	The payment (or refund) ['create_time'], converted using convertToLocalTimeZone.
business	Currently unused
receiver_email1	['payee']['email_address']
receiver_id1	['payee']['merchant_id']
txn_id	The PayPal transaction's['id']
parent_txn_id	An empty string for the original order, otherwise the txn_id of the 'parent' transaction.
num_cart_items	The number of items in the cart when the order was originally placed.
mc_gross	The gross amount of the associated transaction.
mc_fee	Not used; always the default of 0.0000.
payment_gross4	The gross payment, using mc_currency.
payment_fee4	The payment fee.
settle_amount4	The amount settled to the seller's account, in settle_currency.
settle_currency4	The currency in which an amount was settled.
exchange_rate4	Set to any exchange-rate that PayPal used to convert the payment into the seller's PayPal account.
notify_version	The version of the paypalr payment module that created the transaction record. The size of this field was updated to varchar(20) by this payment module, was varchar(6).
verify_sign	Not used.
last_modified	The update_time returned by PayPal for the transaction.
date_added	The create_time returned by PayPal for the transaction.
expiration_time	The expiration_time for authorized orders. Added by this payment module
memo	Contains a JSON-encoded array of additional information relative to the order; only "interesting" for the 'CREATE' txn_type. See below for additional information.

¹ Set only on the primary order record, with a txn_code of CREATE.

² The address-related fields will be NULL for virtual orders (no shipping address provided).

³ Set only when the payment is made with the customer's PayPal Wallet.

⁴ Set only when the associated transaction is a captured payment or a refund.

'memo' Field Contents

The paypal::memo database field contains additional information (JSON-encoded) relative to a PayPal order's initial creation. When decoded, the field is an associative array containing the following elements which are displayed by the transaction's Details modal when viewing in the admin.

Array 'Key'	Description
amount_mismatch	If not an empty array, implies that the amount-breakdown calculated resulted in a total value other than the Zen Cart's calculated amount. The amount-breakdown is, thus, not included in the order registered at PayPal.
cc_info	Included only for initially-captured or authorized credit card payments, containing the credit-card information reported back from PayPal.
seller_protection	The status key of this array identifies what protection PayPal offers the seller for the payment.

PayPal Invoice Format

The invoice sent to PayPal and recorded in the paypal table is of the form

PPR-yyyymmddhhiiss-cid-firstlast-random

Where

• PPR is a fixed value identifying a payment made with this payment module.
• yyyymmddhhiiss is the date/time when the PayPal order was initially created; this value might be some time prior to the order's actual placement!
• cid is the customer_id of the customer placing the order.
• firstlast is the first 3 characters of the customer's first-name followed by the first 3 characters of the customer's last-name.
• random is a random collection of hexadecimal characters, mostly to distinguish any guest-placed orders when the firstlast value isn't unique.