AR Invoice Report for Certain Vendor.

[Former Member]

Hi Experts,

I was needing to have a .txt file created monthly to send to one of the vendors that has the following columns of information (in this order) for an AR Invoice:

Customer city

Customer State

Customer Zip

Part #

Quantity

Invoice #

Invoice Date

Unit cost

Unit Price

Customer ID

Customer Name

Customer PO

Ship Date

Thanks in advance,

Jeff Haldeman

Support One

[Former Member · Accepted solution]

Hello Jef,

Take a look at the "bcp" possiblity in MS SQL which you can put the queryresult in a file and schedule it or else you can create a query and export it manually to excel or txt.

bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -S server -U sa -P password

There's another possibility you can create an add-on for it, but i think the first suggestion should be my choice..

Hope this helps...

Regards,

[Former Member]

Please check if this query meets your need:

Select T2.City 'Customer City', T2.State1 'Customer State', 
T2.ZipCode 'Customer ZIP', T0.ItemCode 'Part#', T0.Quantity, 
T1.DocNum 'Invoice#', T1.DocDate 'Invoice Date', 
T0.GrossBuyPr 'Unit Cost',T0.Price 'Unit Price', T1.CardCode 'Customer Code', T1.CardName 'Customer 
Name', T1.NumAtCard 'Customer PO', T0.ShipDate 'Ship Date'
From INV1 T0 INNER JOIN OINV T1 ON T1.DocEntry = 
T0.DocEntry
LEFT JOIN OCRD T2 ON T2.CardCode = T1.CardCode
LEFT JOIN OITM T3 On T3.ItemCode = T0.ItemCode
Where T1.DocType = 'I' and DateDiff(MM,T1.DocDate,
 GetDate()) = 1

In this query, only the invoices are showed. If you need Credit Memo too, use Union All to add those tables.

Thanks,

Gordon