Authentication

In order to connect you Paypal account with Leapfin, you need to request Paypal to enable SFTP on your production account.

Afterwards, follow the instruction here to set up and a create a SFTP account. The following reports are required by Leapfin:

📘

With SFTP enabled, you will have access to the Activity Download Report. This report allows you to customize reports through selected ranges, such as date range, file type, and data fields.

With access to the Activity Download report and understanding of potential t-codes, this should give you a general idea of what to expect with a settlement report.

Leapfin needs following information regarding the SFTP account. Please contact Paypal support for the information.

required fields:

  • Server host name
  • User name
  • Password or Private key (either one of them will be sufficient)

optional fields:

  • Private key password (required only if SFTP private key is password protected)
  • Outbox or folder name (required only if the files are not placed in the root directory)
  • Host key or Host key fingerprint (required only if you need enhanced security. Providing one of them will be sufficient)
  • File decryption private key (required only if the files on SFTP server are encrypted)
  • File decryption private key password (required only if the files on SFTP server are encrypted using a password protected key)

Data Sources

Once authenticated, Leapfin will fetch and process two types of reports from PayPal SFTP: Transaction Detail Report and Settlement Report.

Both reports contain multiple row types, which are represented by a 2-letter code. The main contents of the reports are data rows with code "SB". Leapfin loads only those records on the Platform.

🚧

Loading historical data

If historical data should be loaded on Leapfin Platform, you need to request Paypal to upload the historical reports to the SFTP location.

Transaction Detail Report

The Transaction Detail Report is generated and put on PayPal's website and the Secure Dropbox by no later than 12:00PM daily in the leading timezone of the reporting window

For each data row (type "SB") from the PayPal's Transaction Detail Report, it is imported as paypal-sftp-transaction into Leapfin. For the schema of the PayPal Transaction Detail Report, look here

Sample Data

{
    "Item ID": "",
    "Card Type": "",
    "Item Name": "Subscription Yearly Plan",
    "Last Name": "Doe",
    "Fee Amount": "3950",
    "First Name": "Jane",
    "Invoice ID": "123456",
    "Consumer ID": "RZXXXXXXXXXX",
    "Auction Site": "",
    "Custom Field": "",
    "Fee Currency": "USD",
    "Option 1 Name": "",
    "Option 2 Name": "",
    "Shipping Name": "Jane",
    "Option 1 Value": "",
    "Option 2 Value": "",
    "Payment Source": "Others",
    "Transaction ID": "001231231231312",
    "Shipping Amount": "0",
    "Shipping Method": "",
    "Auction Buyer ID": "",
    "Insurance Amount": "",
    "Sales Tax Amount": "",
    "Transaction Note": "",
    "Payer's Account ID": "[email protected]",
    "Billing Address Zip": "34480",
    "Fee Debit or Credit": "DR",
    "PayPal Reference ID": "B-SDFASOFMEWQANRP",
    "Payment Tracking ID": "02",
    "Transaction Subject": "",
    "Auction Closing Date": "",
    "Billing Address City": "Austin",
    "Payer Address Status": "N",
    "Shipping Address Zip": "",
    "Transactional Status": "S",
    "Billing Address Line1": "Real Address",
    "Billing Address Line2": "",
    "Billing Address State": "FL",
    "Shipping Address City": "",
    "Consumer Business Name": "Jane Doe",
    "Protection Eligibility": "01",
    "Shipping Address Line1": "",
    "Shipping Address Line2": "",
    "Shipping Address State": "",
    "Transaction Event Code": "T0003",
    "Billing Address Country": "US",
    "Gross Transaction Amount": "238800",
    "PayPal Reference ID Type": "",
    "Shipping Address Country": "",
    "Gross Transaction Currency": "USD",
    "Authorization Review Status": "Tyner",
    "Transaction Completion Date": "2021/02/23 14:58:39 -0800",
    "Transaction Debit or Credit": "CR",
    "Transaction Initiation Date": "2021/02/23 14:58:39 -0800"
}

Settlement Report

For each data row (type "SB") from the PayPal Settlement Report, it is imported as paypal-sftp-settlement into Leapfin. For the schema of the PayPal Settlement Report, look here

Sample Data

{
    "Fee Amount": "27",
    "Invoice ID": "400008",
    "Consumer ID": "WR6DQ7H3H3H36",
    "Custom Field": "monthly_subscription",
    "Fee Currency": "USD",
    "Transaction ID": "008282345I7049831",
    "Fee Debit or Credit": "DR",
    "PayPal Reference ID": "B-0X111174V01010101",
    "Payment Tracking ID": "",
    "Transaction Event Code": "T0003",
    "Gross Transaction Amount": "1000",
    "PayPal Reference ID Type": "",
    "Gross Transaction Currency": "USD",
    "Transaction Completion Date": "2020/09/26 08:47:23 -0700",
    "Transaction Debit or Credit": "CR",
    "Transaction Initiation Date": "2020/09/26 08:47:23 -0700"
}

Mapping Logics

paypal-sftp-transaction

Each data row (type "SB") from PayPal's Transaction Detail Report is imported as a paypal-sftp-transaction record on Leapfin Platform. "Transaction ID" on the report represent a unique transaction. As the transaction pass through different "Transactional Status", a data row is created on the report. The combination of "Transaction ID" and "Transactional Status" uniquely defines a data row on the Transaction Detail Report.

When mapping data, Leapfin creates various types of Financial Records based on the "Transaction Event Code" (i.e. T-Codes) of paypal-sftp-transaction record.

Create a Payment

If paypal-sftp-transaction."Transaction Event Code" matches "T00nn", a Payment record will be created.

📘

"T00nn" represents any T-Codes that start with "T00". In other words, "n" represents any value between 0 and 9.

Target: LeapfinPayment (schema)Source: paypal-sftp-transaction (schema)
objectType"payment"
idpaypal-sftp-transaction."Transaction ID"
amountpaypal-sftp-transaction."Gross Transaction Amount".
if paypal-sftp-transaction."Transaction Debit or Credit" = "DR", then flip the sign before storing the amount
currencyCodepaypal-sftp-transaction."Gross Transaction Currency"
datepaypal-sftp-transaction."Transaction Initiation Date"
statusif paypal-sftp-transaction."Transactional Status" is "S" or "V", then set status as "succeeded".
else if the value is "D" or "F", then status as "failed".
else if the value is "P", set status as "pending"
succeededDateif paypal-sftp-transaction."Transactional Status" is "S" or "V", then set succeededDate as paypal-sftp-transaction."Transaction Completion Date"
otherwise, set succeededDate as None
descriptionpaypal-sftp-transaction."Transaction Note"
exchangeRatesNone
customFieldsThe following fields from source record are added:
-- paypal-sftp-transaction."Consumer ID"
--paypal-sftp-transaction."Invoice ID"
-- paypal-sftp-transaction."PayPal Reference ID"
-- paypal-sftp-transaction."Item ID"
-- paypal-sftp-transaction."Transaction Event Code"
--paypal-sftp-transaction."Item Name"
-- paypal-sftp-transaction."Shipping Amount"
-- paypal-sftp-transaction."Insurance Amount"
-- paypal-sftp-transaction."Billing Address Country”
-- paypal-sftp-transaction."Sales Tax Amount"
-- paypal-sftp-transaction."Custom Field"
In addition, add each pair of option name and option value (e.g. paypal-sftp-transaction."Option 1 Name" and paypal-sftp-transaction."Option 1 Value") if it is not empty.
linksNone

Create a Fee (payment fee)

If paypal-sftp-transaction."Transaction Event Code" matches "T00nn" and if paypal-sftp-transaction."Fee Amount" exists, a Fee record will be created.

Target: LeapfinFee (schema)Source: paypal-sftp-transaction (schema)
objectType"fee"
idpaypal-sftp-transaction."Transaction ID"
amountpaypal-sftp-transaction."Fee Amount".
if paypal-sftp-transaction."Fee Debit or Credit" = "CR", then flip the sign before storing the amount
currencyCodepaypal-sftp-transaction."Fee Currency"
datepaypal-sftp-transaction."Transaction Initiation Date"
descriptionNone
exchangeRatesNone
customFieldsNone
linksAdd a link to the Payment record with:
-- objectType = "payment"
-- id = paypal-sftp-transaction."Transaction ID"

Create a Fee (non-payment fee)

If paypal-sftp-transaction."Transaction Event Code" matches "T01nn", a Fee record will be created.

Target: LeapfinFee (schema)Source: paypal-sftp-transaction (schema)
objectType"fee"
idpaypal-sftp-transaction."Transaction ID"
amountpaypal-sftp-transaction."Gross Transaction Amount".
if paypal-sftp-transaction."Transaction Debit or Credit" = "CR", then flip the sign before storing the amount
currencyCodepaypal-sftp-transaction."Gross Transaction Currency"
datepaypal-sftp-transaction."Transaction Initiation Date"
descriptionpaypal-sftp-transaction."Transaction Note"
exchangeRatesNone
customFieldsThe following fields from source record are added:
-- paypal-sftp-transaction."Consumer ID"
-- paypal-sftp-transaction."Invoice ID"
-- paypal-sftp-transaction."PayPal Reference ID"
-- paypal-sftp-transaction."Item ID"
-- paypal-sftp-transaction."Transaction Event Code"
-- paypal-sftp-transaction."Item Name"
-- paypal-sftp-transaction."Custom Field"
In addition, add each pair of option name and option value (e.g. paypal-sftp-transaction."Option 1 Name" and paypal-sftp-transaction."Option 1 Value") if it is not empty.
linksNone

Create a Payout

If paypal-sftp-transaction."Transaction Event Code" matches "T04nn", a Payout record will be created

Target: LeapfinSettlement (schema)Source: paypal-sftp-transaction (schema)
objectType"payout"
idpaypal-sftp-transaction."Transaction ID"
amountpaypal-sftp-transaction."Gross Transaction Amount"
currencyCodepaypal-sftp-transaction."Gross Transaction Currency"
datepaypal-sftp-transaction."Transaction Initiation Date"
statusif paypal-sftp-transaction."Transactional Status" is "S" or "V", then set status as "paid".
else if the value is "D" or "F", then status as "failed".
else if the value is "P", set status as "pending"
descriptionpaypal-sftp-transaction."Transaction Note"
exchangeRatesNone
customFieldsThe following fields from source record are added:
-- paypal-sftp-transaction."Consumer ID"
-- paypal-sftp-transaction."Invoice ID"
-- paypal-sftp-transaction."PayPal Reference ID"
-- paypal-sftp-transaction."Item ID"
-- paypal-sftp-transaction."Transaction Event Code"
-- paypal-sftp-transaction."Item Name"
-- paypal-sftp-transaction."Custom Field"
In addition, add each pair of option name and option value (e.g. paypal-sftp-transaction."Option 1 Name" and paypal-sftp-transaction."Option 1 Value") if it is not empty.
linksNone

Create a Refund

If paypal-sftp-transaction."Transaction Event Code" is "T1107", "T11014", "T11015", "T1108", "T1109", or "T11016", a Refund record will be created.

Target: LeapfinRefund (schema)Source: paypal-sftp-transaction (schema)
objectType"refund"
idpaypal-sftp-transaction."Transaction ID"
amountpaypal-sftp-transaction."Gross Transaction Amount". The following logic should be applied to ensure that the correct sign of amount is mapped on Leapfin Platform.
if paypal-sftp-transaction."Transaction Event Code" is "T1107", "T11014", "T11015" or "T11016" and paypal-sftp-transaction."Transaction Debit or Credit" = "CR", then flip the sign before storing the amount
if paypal-sftp-transaction."Transaction Event Code" is "T1108" or "T1109" and paypal-sftp-transaction."Transaction Debit or Credit" = "DR", then flip the sign before storing the amount
currencyCodepaypal-sftp-transaction."Gross Transaction Currency"
datepaypal-sftp-transaction."Transaction Initiation Date"
statusif paypal-sftp-transaction."Transactional Status" is "S" or "V", then set status as "succeeded".
else if the value is "D" or "F", then status as "failed".
else if the value is "P", set status as "pending"
descriptionpaypal-sftp-transaction."Transaction Note"
exchangeRatesNone
customFieldsThe following fields from source record are added:
-- paypal-sftp-transaction."Consumer ID"
--paypal-sftp-transaction."Invoice ID"
-- paypal-sftp-transaction."PayPal Reference ID"
-- paypal-sftp-transaction."Item ID"
-- paypal-sftp-transaction."Transaction Event Code"
--paypal-sftp-transaction."Item Name"
-- paypal-sftp-transaction."Shipping Amount"
-- paypal-sftp-transaction."Insurance Amount"
-- paypal-sftp-transaction."Billing Address Country”
-- paypal-sftp-transaction."Sales Tax Amount"
-- paypal-sftp-transaction."Custom Field"
In addition, add each pair of option name and option value (e.g. paypal-sftp-transaction."Option 1 Name" and paypal-sftp-transaction."Option 1 Value") if it is not empty.
linksIf paypal-sftp-transaction."Paypal Reference ID Type" is "TXN", add a link to the Payment record with:
-- objectType = "payment"
-- id = paypal-sftp-transaction."Paypal Reference ID" (if exists)

Create a dispute

If paypal-sftp-transaction."Transaction Event Code" is "T1106", "T1110", "T1201", "T1111", or "T1202", a Dispute record will be created.

Target: LeapfinDispute (schema)Source: paypal-sftp-transaction (schema)
objectType"dispute"
idpaypal-sftp-transaction."Transaction ID"
amountpaypal-sftp-transaction."Gross Transaction Amount". The following logic should be applied to ensure that the correct sign of amount is mapped on Leapfin Platform.
if paypal-sftp-transaction."Transaction Event Code" is "T1106", "T1110" or "T1201" and paypal-sftp-transaction."Transaction Debit or Credit" = "CR", then flip the sign before storing the amount
if paypal-sftp-transaction."Transaction Event Code" is "T1111" or "1202" and paypal-sftp-transaction."Transaction Debit or Credit" = "DR", then flip the sign before storing the amount
currencyCodepaypal-sftp-transaction."Gross Transaction Currency"
datepaypal-sftp-transaction."Transaction Initiation Date"
initiatedDateif paypal-sftp-transaction."Transaction Event Code" is "T1106", "T1110" or "T1201" then set value as paypal-sftp-transaction."Transaction Completion Date"
resolvedDateif paypal-sftp-transaction."Transaction Event Code" is "T1111" or "1202" then set value as paypal-sftp-transaction."Transaction Completion Date"
statusIf paypal-sftp-transaction."Transaction Event Code" is "T1106" or "T1201", then set status as "lost"
else if paypal-sftp-transaction."Transaction Event Code" is "T1110", then set status as "pending"
else if paypal-sftp-transaction."Transaction Event Code" is "T1111" or "T1202, then set status as "won"
descriptionpaypal-sftp-transaction."Transaction Note"
exchangeRatesNone
customFieldsThe following fields from source record are added:
-- paypal-sftp-transaction."Consumer ID"
--paypal-sftp-transaction."Invoice ID"
-- paypal-sftp-transaction."PayPal Reference ID"
-- paypal-sftp-transaction."Item ID"
-- paypal-sftp-transaction."Transaction Event Code"
--paypal-sftp-transaction."Item Name"
-- paypal-sftp-transaction."Shipping Amount"
-- paypal-sftp-transaction."Insurance Amount"
-- paypal-sftp-transaction."Billing Address Country”
-- paypal-sftp-transaction."Sales Tax Amount"
-- paypal-sftp-transaction."Custom Field"
In addition, add each pair of option name and option value (e.g. paypal-sftp-transaction."Option 1 Name" and paypal-sftp-transaction."Option 1 Value") if it is not empty.
linksIf paypal-sftp-transaction."Paypal Reference ID Type" is "TXN", add a link to the Payment record with:
-- objectType = "payment"
-- id = paypal-sftp-transaction."Paypal Reference ID" (if exists)