Importing Sales Order With Existing Pre-Auth

Has anyone had experience importing data into Sales Order Entry for a credit card order that has already been pre-authorized outside of Sage 100 ERP? In particular, we're working with Sage 100 ERP Advanced 2013 PU3 and an online store that uses a third-party add-on to process credit card transactions using Sage Payment Solutions and the same merchant key & virtual terminal ID that is used within Sage 100 ERP.

The pre-auth needs to happen from the online store so that if the credit card information is bad, the customer has the opportunity to correct it (otherwise the order won't come into Sage 100 ERP.) After good credit card information is entered and the pre-authorization is processed, we need to import this order into Sage 100 ERP, along with the details on the credit card transaction for the pre-auth. We've setup a VI import for the order itself with no problems, but getting the credit card data to import is another story. We've tried importing the authorization number & transaction ID from the pre-auth (per Sage's directive) but the credit card data failed to import because there wasn't a transaction amount. We added that in then the import failed because there wasn't a credit card GUID. We added that in then it failed because the GUID was not on file.

Any help, recommendations, and/or advice on this subject would be greatly appreciated.

Parents
  • 0

    Anyone who can help with this?  I'm also needing this solution....  Thanks!!

  • 0 in reply to Brad K

    We were able to get this working, although we found it was extremely picky about how the import wanted things (ex. which fields were included, the order in which the fields are listed in the VI job, etc.) We might have also needed an update to a program, I don't remember if it ended up actually being necessary or not though.

    In the VI job, you need your standard header/detail fields (ex. SalesOrderNo, CustomerNo, ItemCode, etc.) then in addition to these fields, you also need the following "PY" fields at the bottom of the import job:

    PY.PaymentSeqNo (we always assign this to "000001")

    PY.PaymentType (should match the H.PaymentType value, ex. "VISA")

    PY.PaymentTypeCategory (we always assign this to "P")

    PY.CreditCardGUID (32 alpha-numeric characters, this comes from Sage Exchange when the pre-auth was done)

    PY.CreditCardAuthorizationNo (6 numeric characters, this comes from Sage Exchange when the pre-auth was done)

    PY.TransactionAmt (this comes from Sage Exchange when the pre-auth was done)

    PY.CreditCardTransactionID (10 alpha-numeric characters, this comes from Sage Exchange when the pre-auth was done)

    If you'd like to see sample data of what should go in these fields, you could do a test credit card order, do the pre-auth, then look at the file SO_SalesOrderPayment in Data File Display & Maintenance. I hope this information helps, this wasn't very fun to figure out & even Sage didn't have the answer.

  • 0 in reply to akkruse

    Attempting to do the same thing. Recently (as in this week) upgraded MAS from ver 4.50 to Sage 100 ERP Standard 2014 due to sunset date for tax table updates. I knew there would be issues re-integrating our website/shopping cart, but couldn't find the right technical person to fully understand my scenario and offer assistance. We use AbleCommerce 7.07 shopping cart, and back in the day I wrote a custom payment gateway connecter to access Sage Payments API. I assume I will now have to dig up the source code for that and alter it to request and capture the Sage GUID for the card, so it can be used during the VI import.

    Ain't upgrading fun? :-(     I guess I'm off to Sage Payments online support in search of the API (web services) guides.  Any tips/pointers/suggestions from anyone that's been down this rabbit-hole would be appreciated.

  • 0 in reply to alanrich

    We've actually had to do what you're talking about since my original post (not only had to import the data, but also had to do the pre-auth from the website and get the GUID). The service they initially said we needed didn't end up working because this did NOT return the GUID we needed. Instead, we ended up needing their Vault service. Here's an excerpt from an e-mail I got from support:

    To get and process against a GUID you will need to use our Vault service.

    Vault: gateway.sagepayments.net/.../wsVault.asmx  - Use this INSERT_CREDIT_CARD_DATA operation to get the GUID.

    Vault Transaction: gateway.sagepayments.net/.../wsVaultBankcard.asmx - Use the  VAULT_BANKCARD_... to process a transaction against the GUID.

    This e-mail came from "[email protected]", and here's the contact information from the e-mail signature of the particular person that was able to help me out:

    Robert Flores

    Integration Consultant

    Sage Payment Solutions

    Office: 1-800-261-0240

    I'm not sure if you know PHP, but hopefully these snippets will help:

    //create guid record in the vault

    $ccGUID = spsCreateVaultRecord($ccNo, $ccExp);

    //got a guid for the credit card, now do the pre-auth

    $preAuth = spsCreatePreAuth($ccGUID, $ccAmt, $ccType, $addrId);

    if ($preAuth) {

    $response = 'success';

    }

    //FUNCTIONS

    function spsCreateVaultRecord($ccNo, $ccExp) {

    // Setting up request parameters as an array. Sensitive data removed

    // note, format of EXPIRATION_DATE parameter: mmyy

    $request = array(

    'M_ID'    => $merchantId,

    'M_KEY'   => $merchantKey,

    'CARDNUMBER'   => $ccNo,

    'EXPIRATION_DATE'   => $ccExp

    );

    // Turn the trace option on for troubleshooting

    $option=array('trace'=>1);

    // Instantiate the class:

    $SOAP = new SoapClient('gateway.sagepayments.net/.../wsVault.asmx, $option);

    // Specify method and make request:

    $response = $SOAP->INSERT_CREDIT_CARD_DATA($request);

    $result = $response->INSERT_CREDIT_CARD_DATAResult->any;

    // Parse the result

    $xml = new SimpleXMLElement($result);

    $guid = $xml->NewDataSet->Table1->GUID;

    return $guid;

    }

    function spsCreatePreAuth($guid, $amt, $cardtype, $addr) {

    $retVal = false;

    // Setting up request parameters as an array. Sensitive data removed

    $request = array(

    'M_ID'    => $merchantId,

    'M_KEY'    => $merchantKey,

    'C_NAME'    => $addrRow['FirstName'] . ' ' . $addrRow['LastName'],

    'C_ADDRESS'    => $addrRow['Address1'],

    'C_CITY'    => $addrRow['City'],

    'C_STATE'    => $stateRow['Abbreviation'],

    'C_ZIP'    => $addrRow['ZipPostalCode'],

    'C_COUNTRY'   => $countryRow['TwoLetterIsoCode'],

    'C_EMAIL' => $addrRow['Email'],

    'GUID' => $guid,

    'T_AMT'    => $amt,

    'T_ORDERNUM'    => $orderNoRow['OrderId']

    );

    // Turn the trace option on for troubleshooting

    $option=array('trace'=>1);

    // Instantiate the class:

    $SOAP = new SoapClient('gateway.sagepayments.net/.../wsVaultBankcard.asmx, $option);

    // Specify method and make request:

    $response = $SOAP->VAULT_BANKCARD_AUTHONLY($request);

    $result = $response->VAULT_BANKCARD_AUTHONLYResult->any;

    if ($result) {

    // Parse the result

    $xml = new SimpleXMLElement($result);

    $ccApproval = $xml->NewDataSet->Table1->APPROVAL_INDICATOR;

    if ($ccApproval == 'A') {

    //Successfully approved

    $retVal = true;

    /*

    TODO: do something with the credit card data needed for the import into Sage

    $guid

    $xml->NewDataSet->Table1->CODE

    $xml->NewDataSet->Table1->REFERENCE

    $amt

    $addr

    $cardtype

    */

    }

    }

    return $retVal;

    }

Reply
  • 0 in reply to alanrich

    We've actually had to do what you're talking about since my original post (not only had to import the data, but also had to do the pre-auth from the website and get the GUID). The service they initially said we needed didn't end up working because this did NOT return the GUID we needed. Instead, we ended up needing their Vault service. Here's an excerpt from an e-mail I got from support:

    To get and process against a GUID you will need to use our Vault service.

    Vault: gateway.sagepayments.net/.../wsVault.asmx  - Use this INSERT_CREDIT_CARD_DATA operation to get the GUID.

    Vault Transaction: gateway.sagepayments.net/.../wsVaultBankcard.asmx - Use the  VAULT_BANKCARD_... to process a transaction against the GUID.

    This e-mail came from "[email protected]", and here's the contact information from the e-mail signature of the particular person that was able to help me out:

    Robert Flores

    Integration Consultant

    Sage Payment Solutions

    Office: 1-800-261-0240

    I'm not sure if you know PHP, but hopefully these snippets will help:

    //create guid record in the vault

    $ccGUID = spsCreateVaultRecord($ccNo, $ccExp);

    //got a guid for the credit card, now do the pre-auth

    $preAuth = spsCreatePreAuth($ccGUID, $ccAmt, $ccType, $addrId);

    if ($preAuth) {

    $response = 'success';

    }

    //FUNCTIONS

    function spsCreateVaultRecord($ccNo, $ccExp) {

    // Setting up request parameters as an array. Sensitive data removed

    // note, format of EXPIRATION_DATE parameter: mmyy

    $request = array(

    'M_ID'    => $merchantId,

    'M_KEY'   => $merchantKey,

    'CARDNUMBER'   => $ccNo,

    'EXPIRATION_DATE'   => $ccExp

    );

    // Turn the trace option on for troubleshooting

    $option=array('trace'=>1);

    // Instantiate the class:

    $SOAP = new SoapClient('gateway.sagepayments.net/.../wsVault.asmx, $option);

    // Specify method and make request:

    $response = $SOAP->INSERT_CREDIT_CARD_DATA($request);

    $result = $response->INSERT_CREDIT_CARD_DATAResult->any;

    // Parse the result

    $xml = new SimpleXMLElement($result);

    $guid = $xml->NewDataSet->Table1->GUID;

    return $guid;

    }

    function spsCreatePreAuth($guid, $amt, $cardtype, $addr) {

    $retVal = false;

    // Setting up request parameters as an array. Sensitive data removed

    $request = array(

    'M_ID'    => $merchantId,

    'M_KEY'    => $merchantKey,

    'C_NAME'    => $addrRow['FirstName'] . ' ' . $addrRow['LastName'],

    'C_ADDRESS'    => $addrRow['Address1'],

    'C_CITY'    => $addrRow['City'],

    'C_STATE'    => $stateRow['Abbreviation'],

    'C_ZIP'    => $addrRow['ZipPostalCode'],

    'C_COUNTRY'   => $countryRow['TwoLetterIsoCode'],

    'C_EMAIL' => $addrRow['Email'],

    'GUID' => $guid,

    'T_AMT'    => $amt,

    'T_ORDERNUM'    => $orderNoRow['OrderId']

    );

    // Turn the trace option on for troubleshooting

    $option=array('trace'=>1);

    // Instantiate the class:

    $SOAP = new SoapClient('gateway.sagepayments.net/.../wsVaultBankcard.asmx, $option);

    // Specify method and make request:

    $response = $SOAP->VAULT_BANKCARD_AUTHONLY($request);

    $result = $response->VAULT_BANKCARD_AUTHONLYResult->any;

    if ($result) {

    // Parse the result

    $xml = new SimpleXMLElement($result);

    $ccApproval = $xml->NewDataSet->Table1->APPROVAL_INDICATOR;

    if ($ccApproval == 'A') {

    //Successfully approved

    $retVal = true;

    /*

    TODO: do something with the credit card data needed for the import into Sage

    $guid

    $xml->NewDataSet->Table1->CODE

    $xml->NewDataSet->Table1->REFERENCE

    $amt

    $addr

    $cardtype

    */

    }

    }

    return $retVal;

    }

Children
  • 0 in reply to akkruse

    Thanks akkruse.

    Since my website's custom gateway to SPS is currently providing a 'Fail or Success' response which includes the Transaction ID, TransAmt, AuthID, AuthDate, AuthTime on a success, I was thinking I could just create a secondary step for my checkout sequence that would insert a "vault" entry (only after a successful Auth) and retrieve the GUID to associate with the order, for the eventual VI import. This presumes that the Authorization No and Transaction ID (obtained in one call) can be combined with the GUID (obtained in a second call) to allow for all the fields necessary to do the VI import. I would certainly like to confirm these presumptions before I start making modifications required to test them.  

    Do you think this approach would work?

    I do not use PHP, but should be able to translate it into C# or VB code to achieve the same functionality.

  • 0 in reply to alanrich

    I'm not really sure, you've already thought of the first thing I thought of that might be a problem (the GUID not being associated with the original transaction). Given how particular they are about things in regards to importing credit card data, I wouldn't be surprised if it didn't work (I might be a little surprised if it did work actually).

    We've ran into issues where there could be good credit card data to import, but the import fails for another reason (ex.only one item on the order and it's an invalid order). If we correct the problem (ex. change the import file to use a valid item) then rerun the import, this time it will fail and say the credit card information is bad (I believe it's the GUID it doesn't like). It's like the first import pulled the credit card information in far enough for SPS to know about it, and from then on it can't be imported again. That was part of the difficulty in getting the import to work (you could test it with valid data but in the wrong sequence, but that test invalidated your otherwise good data so that even in the right sequence it will no longer import).

    Good luck, hopefully you can benefit from some of the pain we've had to go through :)