AR Receipts

[Zak2009]

Anyone with code to create AR Receipts.

 

[Jay Converse]

Record a macro, that's all the code you need.

 

[Zak2009]

I have already done that but it generates the following errors;
1. You must apply this receipt to at least one document when the applied amount is zero.
2. The net balance is Zero for this document.
What am looking for is anyone who might have a macro that has been cleaned.

 

[Jay Converse]

Private Function AddReceipts() As Boolean
Dim iErrors As Integer
Dim iWarnings As Integer
Dim iOrderCount As Long
Dim iTotalCount As Long
Dim bErrorHere As Boolean
Dim iLineCount As Long
Dim iReceiptCount As Long
Dim nFileTotal As Double
Dim sCust As String
Dim sInvoice As String
Dim sCredit As String
Dim sSQL As String
Dim sLine As String
Dim recOrder As New Recordset

On Error GoTo badstuff

'Dim tsin As TextStream
'Dim tsLine As String
'Dim tsRec() As String

'Date, Time, Time Zone, Name, Type, Status, Gross, Fee, Net, From Email Address, To Email Address, Transaction ID, Counterparty Status, Address Status, Item Title, Item ID, Shipping and Handling Amount, Insurance Amount, Sales Tax, Option 1 Name, Option 1 Value, Option 2 Name, Option 2 Value, Auction Site, Buyer ID, Item URL, Closing Date, Escrow Id, Invoice Id, Reference Txn ID, Invoice Number, Custom Number, Receipt ID, Balance, Address Line 1, Address Line 2/District/Neighborhood, Town/City, State/Province/Region/County/Territory/Prefecture/Republic, Zip/Postal Code, Country, Contact Phone Number,
'5/17/2013,06:40:59,PDT, Lillian Caldwell,Express Checkout Payment Received,Completed,18.00,-0.82,17.18,lcaldwell3@woh.rr.com,kzell@ameswalker.com,6CH05807EV7755058,Unverified,Confirmed,,,0.00,,0.00,,,,,,,,,,,58T70239W6376101B,,,,11,232.57,1517 Hochwalt Avenue,,Dayton,OH,45417,United States,,
'5/17/2013,06:40:29,PDT,Lillian Caldwell,Authorization,Completed,18.00,0.00,18.00,kzell@ameswalker.com,lcaldwell3@woh.rr.com,58T70239W6376101B,Unverified,Confirmed,,,0.00,,0.00,,,,,,,,,,,,,storeid=ames-walker&orderid=x1527710084&yahoosign=S3t.wtMsNCH2fL1xux_H5IvE3E0-,,11,215.39,1517 Hochwalt Avenue,,Dayton,OH,45417,United States,,
'5/17/2013,02:55:28,PDT,stephanie zone,Mobile Express Checkout Payment Received,Completed,23.69,-0.99,22.70,StephanieZone@gmail.com,kzell@ameswalker.com,0P026924N6159932L,Verified,Confirmed,,,0.00,,0.00,,,,,,,,,,,6N906121F1686234H,,,,11,215.39,1722 McGee Ave,,Berkeley,CA,94703,United States,,
'5/17/2013,02:55:24,PDT,stephanie zone,Authorization,Completed,23.69,0.00,23.69,kzell@ameswalker.com,StephanieZone@gmail.com,6N906121F1686234H,Verified,Confirmed,,,0.00,,0.00,,,,,,,,,,,,,storeid=ames-walker&orderid=x1790543075&yahoosign=S3t.wtMsNCH2fL1xux_H5IvE3E0-,,11,192.69,1722 McGee Ave,,Berkeley,CA,94703,United States,,
'5/17/2013,00:42:07,PDT,Carolyn Arnow,Express Checkout Payment Received,Completed,24.78,-1.02,23.76,ccarnow@gmail.com,kzell@ameswalker.com,83K562510N0108505,Verified,Confirmed,,,0.00,,0.00,,,,,,,,,,,3BL83561YB584232J,,,,11,192.69,6647 12 Ter No,,Saint Petersburg,FL,33710,United States,,

'Rec_Num Transaction_ID AVS_Street_Match AVS_Zip_Match USD Amount Authcode Billing_Address Billing_City Billing_Company_Name Billing_Country Billing_First_Name Billing_Last_Name Billing_State Billing_Zip Customer_Code Expires Invoice_Number PayPal_Fees Purchase_Order Response_Msg Result_Code Settled_Date Tax_Amount Tender_Type Time Transaction_State Type User
'1 VLFA9FCEE4E6 Y Y USD 1,052.00 188780 77 Thomas Johnson Drive, Suite Frederick Capitol Vein and Laser US Paul McNeil, MD MD 21702 MDSU 14-Jan IN00059450 0 CHA09122013 Approved 0 9/13/13 23:47 0 American Express 9/13/13 10:26 8 Sale tglenn
'2 VLCA9FF1CEBC Y Y USD 27.47 28926 5500 Armstrong Road Battle Creek VAMC Battle Creek US MI 49037 VABC 13-Nov IN00059670 0 515-P45187AM Approved 0 9/16/13 23:32 0 Visa 9/16/13 7:54 8 Sale tglenn
'3 VLCA9FF596B6 Y Y USD 102.8 159183 PO Box 12027 Jackson US Dwight Reppa WY 83002 PLOW 15-Sep IN00059946 0 REPPA Approved 0 9/16/13 23:32 0 American Express 9/16/13 12:46 8 Sale tglenn

Dim sSchemaFile As String
Dim sTempFile As String
Dim tsSchema As TextStream
Dim tsTemp As TextStream
Dim tsIn As TextStream

sTempFile = fso.GetParentFolderName(Me.txtInputFileName) & "\temp.txt"
sSchemaFile = fso.GetParentFolderName(Me.txtInputFileName) & "\schema.ini"

Set tsTemp = fspenTextFile(sTempFile, ForWriting, True)
Set tsIn = fspenTextFile(Me.txtInputFileName, ForReading)
Set tsSchema = fspenTextFile(sSchemaFile, ForWriting, True)

tsSchema.WriteLine "[temp.txt]"
tsSchema.WriteLine "ColNameHeader=FALSE"
tsSchema.WriteLine "format=TabDelimited"
tsSchema.WriteLine " col1 = Rec_Num text"
tsSchema.WriteLine " col2 = Transaction_ID text"
tsSchema.WriteLine " col3 = AVS_Street_Match text"
tsSchema.WriteLine " col4 = AVS_Zip_Match text"
tsSchema.WriteLine " col5 = USD text"
tsSchema.WriteLine " col6 = Amount double"
tsSchema.WriteLine " col7 = Authcode text"
tsSchema.WriteLine " col8 = Billing_Address text"
tsSchema.WriteLine " col9 = Billing_City text"
tsSchema.WriteLine " col10 = Billing_Company_Name text"
tsSchema.WriteLine " col11 = Billing_Country text"
tsSchema.WriteLine " col12 = Billing_First_Name text"
tsSchema.WriteLine " col13 = Billing_Last_Name text"
tsSchema.WriteLine " col14 = Billing_State text"
tsSchema.WriteLine " col15 = Billing_Zip text"
tsSchema.WriteLine " col16 = Customer_Code text"
tsSchema.WriteLine " col17 = Expires text"
tsSchema.WriteLine " col18 = Invoice_Number text"
tsSchema.WriteLine " col19 = PayPal_Fees text"
tsSchema.WriteLine " col20 = Purchase_Order text"
tsSchema.WriteLine " col21 = Response_Msg text"
tsSchema.WriteLine " col22 = Result_Code text"
tsSchema.WriteLine " col23 = Settled_Date text"
tsSchema.WriteLine " col24 = Tax_Amount double"
tsSchema.WriteLine " col25 = Tender_Type text"
tsSchema.WriteLine " col26 = Time text"
tsSchema.WriteLine " col27 = Transaction_State text"
tsSchema.WriteLine " col28 = Transtype text"
tsSchema.WriteLine " col29 = User text"
tsSchema.Close

' Skip the first 2 lines
'sLine = tsin.ReadLine
'sLine = tsIn.ReadLine

Do Until tsIn.AtEndOfStream
sLine = tsIn.ReadLine
If Trim(sLine) <> "" Then tsTemp.WriteLine sLine
Loop

tsIn.Close
tsTemp.Close

If cnCSVFolder.State <> 0 Then cnCSVFolder.Close
cnCSVFolder.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fso.GetParentFolderName(Me.txtInputFileName) & ";Extended Properties=""text;HDR=No;FMT=TabDelimited"""

sSQL = "SELECT * FROM [temp.txt] where Transaction_State = '8'"

If recOrder.State <> 0 Then recOrder.Close
recOrder.Open sSQL, cnCSVFolder, adOpenForwardOnly, adLockReadOnly

If Not recOrder.EOF Then
ArReceiptbatch.Cancel
ArReceiptbatch.Fields("CODEPYMTYP").PutWithoutVerification ("CA")
ArReceiptbatch.RecordGenerate True
ArReceiptbatch.Fields("BATCHDESC") = "Payflow Import " & fso.GetFileName(Me.txtInputFileName)
ArReceiptbatch.Fields("DATEBTCH") = recOrder.Fields(Settled_Date)
ArReceiptbatch.Update

ArRefundbatch.Cancel
'ArRefundbatch.Fields("CODEPYMTYP").PutWithoutVerification ("CA")
ArRefundbatch.RecordGenerate True
ArRefundbatch.Fields("BTCHDESC") = "Payflow Import " & fso.GetFileName(Me.txtInputFileName)
ArRefundbatch.Fields("BTCHDATE") = recOrder.Fields(Settled_Date)
ArRefundbatch.Update
End If

Do While Not recOrder.EOF

iLineCount = iLineCount + 1

' Find the invoice by email and address
' First customer, then OE invoices

If NoNull(recOrder.Fields(Rec_Num)) <> "" Then ' Skip blank lines
iReceiptCount = iReceiptCount + 1

nFileTotal = nFileTotal + recOrder.Fields(Amount)
sInvoice = recOrder.Fields(Invoice_Number)

Me.lblStatus = recOrder.Fields(Customer_Code) & "/" & recOrder.Fields(Invoice_Number)
DoEvents

If UCase(Trim(recOrder.Fields(Trans_Type))) = "CREDIT" Then
If FindCredit(sCust, sCredit, recOrder) Then
If ArInvoiceRead.Fields("amtduehc") = 0 Then
LogWrite "Error: Transaction ID " & recOrder.Fields(Transaction_ID) & ", customer " & recOrder.Fields(Customer_Code) & ", amount " & Format(recOrder.Fields(Amount), "0.00") & ", credit note " & sCredit & " is already paid"
iErrors = iErrors + 1
Else
If Abs(ArInvoiceRead.Fields("amtduehc")) <> recOrder.Fields(Amount) Then
LogWrite "Error: Transaction ID " & recOrder.Fields(Transaction_ID) & ", customer " & recOrder.Fields(Customer_Code) & ", credit note " & sCredit & ", amount " & Format(recOrder.Fields(Amount), "0.00") & " is different than the credit note balance of " & ArInvoiceRead.Fields("amtduehc")
iErrors = iErrors + 1
Else
'Invalid entry. Value of ARRFH.AMTTC does not equal AMTTCCA + AMTTCCK + AMTTCCC

ArRefundheader.Cancel
ArRefundheader.RecordGenerate False
ArRefundheader.Fields("IDCUST").Value = sCust
ArRefundheader.Fields("DOCDATE").Value = recOrder.Fields(Settled_Date)
ArRefundheader.Fields("DOCDESC").Value = recOrder.Fields(Transaction_ID)
'ArRefundheader.Fields("AMTTC").Value = recOrder.Fields(Amount)
ArRefundheader.Process
ArRefunddetail1.RecordGenerate False

ArRefunddetail1.Fields("IDINVC").Value = sCredit
ArRefunddetail1.Fields("paymtype").Value = 3 ' credit card
ArRefunddetail1.Fields("AMTPC").Value = recOrder.Fields(Amount)
ArRefunddetail1.Fields("PROCESSCMD").Value = "1" ' Process Command
ArRefunddetail1.Process
ArRefunddetail1.Insert
ArRefundheader.Insert
End If
End If
Else
LogWrite "Error: Transaction ID " & recOrder.Fields(Transaction_ID) & ", customer '" & recOrder.Fields(Customer_Code) & "', amount " & Format(recOrder.Fields(Amount), "0.00") & ", credit note '" & recOrder.Fields(Purchase_Order) & "' cannot be found for a refund"
iErrors = iErrors + 1
End If
Else
If FindInvoice(sCust, sInvoice, recOrder) Then
ArInvoiceRead.Cancel
ArInvoiceRead.Order = 2
ArInvoiceRead.Browse "IDINVC = """ & sInvoice & """", True
ArInvoiceRead.Fetch

If ArInvoiceRead.Fields("swpaid") = 1 Then
LogWrite "Error: Transaction ID " & recOrder.Fields(Transaction_ID) & ", customer " & recOrder.Fields(Customer_Code) & ", amount " & Format(recOrder.Fields(Amount), "0.00") & ", invoice " & sInvoice & " is already paid"
iErrors = iErrors + 1
Else
If recOrder.Fields(Amount) > ArInvoiceRead.Fields("amtduehc") Then
LogWrite "Error: Transaction ID " & recOrder.Fields(Transaction_ID) & ", customer " & recOrder.Fields(Customer_Code) & ", amount " & Format(recOrder.Fields(Amount), "0.00") & " is greater than the balance " & Format(ArInvoiceRead.Fields("amtduehc"), "0.00") & " on invoice " & sInvoice
iErrors = iErrors + 1
Else
ArReceiptheader.RecordGenerate False
ArReceiptheader.Fields("IDCUST").Value = sCust
ArReceiptheader.Fields("IDRMIT").Value = recOrder.Fields(Transaction_ID)
ArReceiptheader.Fields("CODEPAYM").Value = "PAYPAL"
ArReceiptheader.Fields("DATERMIT").Value = recOrder.Fields(Settled_Date)
ArReceiptdetail1.Cancel
ArReceiptheader.Process
ArReceiptheader.Fields("AMTRMIT").Value = recOrder.Fields(Amount)
ArReceiptdetail1.Cancel
ArReceiptheader.Process
ArReceiptdetail1.RecordClear
ArReceiptdetail1.RecordGenerate False
' ArReceiptheader.Fields("DOCTYPE").Value = "2" ' Document Type

ArReceiptheader.Fields("AMTRMIT").Value = recOrder.Fields(Amount)
ArReceiptdetail1.Fields("idinvc") = sInvoice

ArReceiptdetail1.Fields("AMTPAYM").PutWithoutVerification (recOrder.Fields(Amount))

ArReceiptdetail1.Insert
ArReceiptheader.Insert
If recOrder.Fields(Amount) <> ArInvoiceRead.Fields("amtduehc") Then
LogWrite "Transaction ID " & recOrder.Fields(Transaction_ID) & ", customer " & sCust & ", invoice " & sInvoice & ", amount " & Format(recOrder.Fields(Amount), "0.00") & " paid on balance of " & ArInvoiceRead.Fields("amtduehc")
End If
End If
End If
Else
sCust = recOrder.Fields(Customer_Code)
ArCustomerRead.Browse "IDCUST = """ & sCust & """", True
If ArCustomerRead.Fetch Then
ArReceiptheader.RecordGenerate False
ArReceiptheader.Fields("IDCUST").Value = sCust
ArReceiptheader.Fields("IDRMIT").Value = recOrder.Fields(Transaction_ID)
ArReceiptdetail1.Cancel
ArReceiptheader.Process
ArReceiptheader.Fields("AMTRMIT").Value = recOrder.Fields(Amount)
ArReceiptdetail4.Fields("IDCUST").Value = sCust
ArReceiptdetail4.Fields("AMTRMIT").Value = recOrder.Fields(Amount)
ArReceiptdetail4.Fields("PROTYPE").PutWithoutVerification ("2") ' Process Type
ArReceiptdetail4.Process
ArReceiptheader.Insert
LogWrite "Warning: Transaction ID " & recOrder.Fields(Transaction_ID) & ", customer " & sCust & ", invoice " & sInvoice & " was not found, amount " & Format(recOrder.Fields(Amount), "0.00") & " applied to open invoices"
iWarnings = iWarnings + 1
Else
LogWrite "Error: Transaction ID " & recOrder.Fields(Transaction_ID) & ", unable to determine customer or invoice from customer code " & sCust & ", invoice " & recOrder.Fields(Invoice_Number) & ", amount " & Format(recOrder.Fields(Amount), "0.00")
iErrors = iErrors + 1
End If
End If ' Found an invoice
End If ' Is a Payment
End If 'Not null records
recOrder.MoveNext
Loop


LogWrite "", True
LogWrite iReceiptCount & " transactions in the file", True
LogWrite Format(nFileTotal, "0.00") & " total amount in the file", True
LogWrite ArReceiptbatch.Fields("CNTENTER") & " receipts added", True
LogWrite Format(ArReceiptbatch.Fields("AMTENTER"), "0.00") & " total receipt amount", True
LogWrite ArRefundbatch.Fields("ENTRYCNT") & " refunds added", True
LogWrite Format(ArRefundbatch.Fields("ENTRYTOT"), "0.00") & " total refund amount", True
LogWrite iWarnings & " warnings ", True
LogWrite iErrors & " errors", True

Exit Function

badstuff:
iErrors = iErrors + 1
Dim lCount As Long
Dim lIndex As Long
lCount = Errors.Count
LogWrite "Error input line number " & iLineCount, True
If lCount = 0 Then
LogWrite Err.Description, True
Else
For lIndex = 0 To lCount - 1
LogWrite Errors.Item(lIndex), True
Next
Errors.Clear
End If

bErrorHere = True
AddReceipts = False

Resume Next

End Function