Sales Order Payment Table

Posted By FormerMember over 2 years ago

Hi Sage Family,

I'm trying to create a SO_SalesOrderPayment record using an external BOI script. Ebridge is creating the Sales Order and Customer record. The client didn't find out until implemtation that the CC pre-authorization wasn't being done. I was asked to create a BOI script to take a .CSV files with the CC info and create the S/O payment record. I used the following ProvideX example to get me started. After reading the notes at the bottom of the code it wasn't tested or validated to be working.

I was hoping the folks here on the forum might have already done this and get me going on the right path again. On my laptop I don't have a CC processor configured but the client uses Paya. I was able to create the payment record on my laptop but it doesn't have the values I set with the SetValue() method. I'm getting two errors on the client's system saying the Payment ID can't be found (exists in AR_CustomerCreditCard) and on the Write() it says that there are no detail lines or a total amount. (there is)

Example from Sage Employee

orderBus = NEW("SO_SalesOrder_bus", %SYS_SS)
retVal = orderBus'SetKeyValue("SalesOrderNo$", "0000210")
retVal = orderBus'SetKey()
IF retVal {
       retVal = orderBus'PaymentObj'SetKeyValue("SalesOrderNo$", "0000210")
       retVal = orderBus'PaymentObj'SetKeyValue("PaymentSeqNo$", "000001")
       retVal = orderBus'PaymentObj'SetKey()
       IF retVal {
            retVal = orderBus'PaymentObj'SetValue("PaymentTypeCategory$", "P")
            retVal = orderBus'PaymentObj'SetValue("PaymentType$", "CC")
            retVal = orderBus'PaymentObj'SetValue("CreditCardGUID$", "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
            retVal = orderBus'PaymentObj'SetValue("Last4UnencryptedCreditCardNos$", "1111")
            retVal = orderBus'PaymentObj'SetValue("ExpirationDateYear$", "2015")
            retVal = orderBus'PaymentObj'SetValue("ExpirationDateMonth$", "09")
            retVal = orderBus'PaymentObj'SetValue("CreditCardAuthorizationNo$", "xxxxx")
            retVal = orderBus'PaymentObj'SetValue("TransactionAmt", 74.00)
            retVal = orderBus'PaymentObj'SetValue("CreditCardTransactionID$", "xxxxxxxxxx")
            retVal = orderBus'PaymentObj'Write()
       }
      ! write order
      retVal = orderBus'Write()
      retVal = orderBus'Clear()
 } 

DROP OBJECT orderBus

Notes

"CC" is a sage exchange processing payment type.
I don't have a real pre-authorization so I am not 100% sure this works.
P.S. the CreditCardGUID$ may need to be on-file in Sage 100.

Here is my ScriptBasic external script. It's traditional BASIC so it shouldn't be too hard to follow.

IMPORT com.sbi

OPEN "payments.txt" FOR INPUT AS #1
LINE INPUT #1, cc_raw
CLOSE(1)

OPEN "preauth_error.log" FOR OUTPUT AS #2

SUB Log_Error(obj, desc)
  LOCAL errnum, errmsg
  errnum = COM::CBN(obj, "sLastErrorNum", :GET)
  errmsg = COM::CBN(obj, "sLastErrorMsg", :GET)
  PRINT #2, FORMATDATE("YEAR-0M-0D 0H:0m:0s"), " - ", desc, " - ", errnum, " - ", errmsg, "\n"
  COM::CBN(obj, "sLastErrorNum", :LET, "")
  COM::CBN(obj, "sLastErrorMsg", :LET, "")
END SUB  

SPLITA CHOMP(cc_raw) BY "|" TO cc_data

so_number = cc_data[0]
pymt_type = UCASE(cc_data[3])
cc_id = UCASE(cc_data[4])
cc_guid = cc_data[5]
cc_type = cc_data[6]
last_4 = cc_data[7]
yy_exp = "20" & RIGHT(cc_data[8], 2)
mm_exp = LEFT(cc_data[8], 2)
cc_holder = cc_data[10]
tran_id = cc_data[11]
auth_num = cc_data[12]
SPLIT cc_data[13] BY "/" TO d_mm, d_dd, d_yyyy
auth_date = d_yyyy & d_mm & d_dd
SPLIT cc_data[14] BY ":" TO t_hr, t_min, t_sec
auth_t = FORMAT("%2.5f", t_hr + t_min / 60 + t_sec / 3600)
addr_1 = cc_data[15]
addr_2 = cc_data[16]
city = UCASE(cc_data[17])
state = cc_data[18]
zip = cc_data[19]
IF cc_data[20] = "US" THEN
  country = "USA"
ELSE
  country = cc_data[20]
END IF
email = cc_data[21]
tran_amt = cc_data[23]

oscript = COM::CREATE(:SET, "ProvideX.Script")
COM::CBN(oScript, "Init", :CALL, "C:\\Sage\\Sage 100 Standard\\MAS90\\Home")
osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
COM::CBN(osession, "nSetUser", :CALL, "UserID", "Password")
COM::CBN(osession, "nsetcompany", :CALL, "ABC")
COM::CBN(osession, "nSetDate", :CALL, "S/O", "20211212")
COM::CBN(osession, "nSetModule", :CALL, "S/O")
COM::CBN(osession, "nSetProgram", :CALL, COM::CBN(osession, "nLookupTask", :CALL, "SO_SalesOrder_ui"))
orderbus = COM::CBN(oscript, "NewObject", :SET, "SO_SalesOrder_bus", osession)
opymt = COM::CBN(orderbus, "oPaymentObj", :GET)

IF COM::CBN(orderbus, "nSetKeyValue", :CALL, "SalesOrderNo$", so_number) = 0 THEN Log_Error(orderbus, "SetValue(SalesOrderNo$)")
IF COM::CBN(orderbus, "nSetKey", :CALL) = 0 THEN Log_Error(orderbus, "SetKey")
IF COM::CBN(orderbus, "nSetValue", :CALL, "PaymentType$", "CC") = 0 THEN Log_Error(orderbus, "SetValue(PaymentType$)")
IF COM::CBN(orderbus, "nSetValue", :CALL, "PaymentTypeCategory$", "P") = 0 THEN Log_Error(orderbus, "SetValue(PaymentTypeCategory$)")

COM::CBN(opymt, "sLastErrorNum", :LET, "")
COM::CBN(opymt, "sLastErrorMsg", :LET, "")

IF COM::CBN(opymt, "nSetKeyValue", :CALL, "SalesOrderNo$", so_number) = 0 THEN Log_Error(opymt, "SetKeyValue(SalesOrderNo$)")
IF COM::CBN(opymt, "nSetKeyValue", :CALL, "PaymentSeqNo$", "000001") = 0 THEN Log_Error(opymt, "SetKeyValue(PaymentSeqNo$)")
IF COM::CBN(opymt, "nSetKey", :CALL) = 0 THEN Log_Error(opymt, "SetKey")

IF COM::CBN(opymt, "nSetValue", :CALL, "PaymentType$", "CC") = 0 THEN Log_Error(opymt, "SetValue(PaymentType$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "PaymentTypeCategory$", "P") = 0 THEN Log_Error(opymt, "SetValue(PaymentTypeCategory$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CreditCardID$", cc_id) = 0 THEN Log_Error(opymt, "SetValue(CreditCardID$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CreditCardGUID$", cc_guid) = 0 THEN Log_Error(opymt, "SetValue(CreditCardGUID$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CardType$", cc_type) = 0 THEN Log_Error(opymt, "SetValue(CardType$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "ExpirationDateYear$", yy_exp) = 0 THEN Log_Error(opymt, "SetValue(ExpirationDateYear$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "ExpirationDateMonth$", mm_exp) = 0 THEN Log_Error(opymt, "SetValue(ExpirationDateMonth$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CorporateTaxOverrd$", "N") = 0 THEN Log_Error(opymt, "SetValue(CorporateTaxOverrd$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CardholderName$", cc_holder) = 0 THEN Log_Error(opymt, "SetValue(CardholderName$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CreditCardTransactionID$", tran_id) = 0 THEN Log_Error(opymt, "SetValue(CreditCardTransactionID$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CreditCardAuthorizationNo$", auth_num) = 0 THEN Log_Error(opymt, "SetValue(CreditCardAuthorizationNo$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AuthorizationDate$", auth_date) = 0 THEN Log_Error(opymt, "SetValue(AuthorizationDate$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AuthorizationTime$", auth_time) = 0 THEN Log_Error(opymt, "SetValue(AuthorizationTime$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AVSAddressLine1$", addr_1) = 0 THEN Log_Error(opymt, "SetValue(AVSAddressLine1$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AVSAddressLine1$", addr_2) = 0 THEN Log_Error(opymt, "SetValue(AVSAddressLine1$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AVSCity$", city) = 0 THEN Log_Error(opymt, "SetValue(AVSCity$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AVSState$", state) = 0 THEN Log_Error(opymt, "SetValue(AVSState$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AVSZipCode$", zip) = 0 THEN Log_Error(opymt, "SetValue(AVSZipCode$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "AVSCountryCode$", country) = 0 THEN Log_Error(opymt, "SetValue(AVSCountryCode$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "CreditCardEmailAddress$", email) = 0 THEN Log_Error(opymt, "SetValue(CreditCardEmailAddress$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "SaveCreditCard$", "N") = 0 THEN Log_Error(opymt, "SetValue(SaveCreditCard$)")
IF COM::CBN(opymt, "nSetValue", :CALL, "TransactionAmt", tran_amt) = 0 THEN Log_Error(opymt, "SetValue(TransactionAmt)")

COM::CBN(orderbus, "sLastErrorNum", :LET, "")
COM::CBN(orderbus, "sLastErrorMsg", :LET, "")

IF COM::CBN(orderbus, "nWrite", :CALL) = 0 THEN Log_Error(orderbus, "Write")
IF COM::CBN(orderbus, "nClear", :CALL) = 0 THEN Log_Error(orderbus, "Clear")

COM::CBN(orderbus, "DropObject", :CALL)
COM::CBN(osession, "DropObject", :CALL)
COM::RELEASE(oscript)
CLOSE(2)

I'm hoping someone will see what I'm missing.

Error Log

2021-12-22 08:08:44 - SetValue(CreditCardTransactionID$) - CI_CannotSet - You cannot set Payment Transaction ID for this Payment Type.

2021-12-22 08:08:44 - SetValue(TransactionAmt) - CI_CannotSet - You cannot set the payment authorization amount when enable credit card processing has not been selected in Company Maintenance for this company or the payment type entered has not been configured to process credit card transactions.

FormerMember over 2 years ago

I think I found the problem. I need to add this method call after creating the opymt object.

COM::CBN(opymt, "nAddPayment", :CALL)

I won't be able to test this theory until Monday.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
FormerMember over 2 years ago in reply to FormerMember

I was able to get this to work locally without a CC processor configured, As it turns out I needed to do the Write() method call on the payment object as well as the SO_SalesOrder_bus object.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
FormerMember over 2 years ago

I'm working on an external BOI project to integrate credit card authorizations to fill a hole eBridge left only creating the Sales Order and Customer record. I can't find a way to get CreditCardTransactionID$ to populate with the SO_SalesOrderPayment record. I also have to create the AR_CustomerCreditCard to get it to write the record.

This is the error I'm getting when trying to do a SetValue() method for CreditCardTransactionID$.

CI_CannotSet - You cannot set Payment Transaction ID for this Payment Type.

Is this telling me I can't set CreditCardTransactionID$ with a preauthorization payment type?

Ideally I don't want to create the AR_CustomerCreditCard record as the client doesn't save CC info. I noticed here on Sage City a post where these two properties when true bypass the CC on file check but doesn't seem to work.

IgnoreNOF

SkipClearCCInfo

If anyone has been able to create S/O authorization payment records with CreditCardTransactionID$ and not have a CC on file, I would love to see your code.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
FormerMember over 2 years ago in reply to FormerMember

If the CreditCardTransactionID$ isn't set in the SO_SalesOrderPayment record when the client tries to process the order it says the card / transaction is expired. If we DFD&M the value in then it processes fine,.

Can someone from the Sage 100 dev team chime in and provide some guidance?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
Natasha Chang over 2 years ago in reply to FormerMember

Did you set up payment type "CC" as a Paya credit card payment type.in A/R Payment Type Maintenance (ie. you entered the Paya Virtual Terminal ID and Merchant Key)?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
FormerMember over 2 years ago in reply to Natasha Chang

The client is using their own payment type codes. (WVISA) My local tests using CC has the same issue.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
Natasha Chang over 2 years ago in reply to FormerMember

Whatever code you use, is the Paya Virtual Terminal ID and Merchant Key set up in AR Payment Type Maintenance? I assume that "Credit Card' is selected in Company Maintenance - Payment tab - Enable Payments drop box?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
FormerMember over 2 years ago in reply to Natasha Chang

For standard call in orders their CC processing with Paya works fine. Only these preauth payment records I'm creating have the issuse of not being able to set the transaction ID.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
Natasha Chang over 2 years ago in reply to FormerMember

From the error message you get, it looks like your payment type isn't a paya payment type. Also, CreditCardTransactionID should be set after CreditCardAuthorizationNo and TransactionAmt, (You should get a different message when you set them in the wrong order.)

You can add credit card information to an order without saving the card info in AR_CustomerCreditCard. However, the credit card GUID will be removed from the vault when the order is invoiced and updated.

orderBus = NEW("SO_SalesOrder_bus", %sys_ss)

retVal = orderBus'SetKeyValue("SalesOrderNo$", "0000999")
retVal = orderBus'SetKey()
IF retVal {
retVal = orderBus'SetValue("ARDivisionNo$", "01")
retVal = orderBus'SetValue("CustomerNo$", "ABF")
! add line
retVal = orderBus'Lines'AddLine()
retVal = orderBus'Lines'SetValue("ItemCode$", "1001-HON-H252")
retVal = orderBus'Lines'SetValue("QuantityOrdered", 5)
retVal = orderBus'Lines'Write()
! - calculate sales tax first
retVal = orderBus'SalesTaxCalculation() ! to make sure DocumentTotal is correct if it is used

retVal = orderBus'PaymentObj'AddDeposit()
IF retVal {
retVal = orderBus'PaymentObj'SetValue("PaymentType$", "CC")
retVal = orderBus'PaymentObj'SetValue("CreditCardGUID$", "8aa22a765b704ff49f459d7fb81f76d0")
retVal = orderBus'PaymentObj'SetValue("TransactionAmt", 100)
retVal = orderBus'PaymentObj'SetValue("CreditCardAuthorizationNo$", "xxxxx")
retVal = orderBus'PaymentObj'SetValue("CreditCardTransactionID$", "yyyyy")
retVal = orderBus'PaymentObj'SetValue("CreditCardTranckingID$", "zzzzz")
retVal = orderBus'PaymentObj'Write()
}
! add payment
retVal = orderBus'PaymentObj'AddPayment()
IF retVal {
retVal = orderBus'PaymentObj'SetValue("PaymentType$", "CC")
retVal = orderBus'PaymentObj'SetValue("CreditCardGUID$", "8aa22a765b704ff49f459d7fb81f76d0")
retVal = orderBus'PaymentObj'SetValue("TransactionAmt", orderBus'DocumentTotal) ! or any amount
retVal = orderBus'PaymentObj'SetValue("CreditCardAuthorizationNo$", "11111")
retVal = orderBus'PaymentObj'SetValue("CreditCardTransactionID$", "22222")
retVal = orderBus'PaymentObj'SetValue("CreditCardTranckingID$", "33333")
retVal = orderBus'PaymentObj'Write()
}
! write order
retVal = orderBus'Write()
retVal = orderBus'Clear()
}

DROP OBJECT orderBus
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
FormerMember over 2 years ago in reply to Natasha Chang

Thanks for the help and code example. This gives me something to go on. I'll report back after validating your suggestions.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Sage 100

Sales Order Payment Table

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING