SmartList showing Tax Detail ID for each unposted SOP order

(1) Share

Report

Posted on by Community Member

I'd like to create a SmartList report showing each tax detail id for all unposted SOP orders. I have tried using SmartList designer, but I have not found the correct View. Ideally, I would like the report to include the tax schedule id from the customer master.

Appreciate any help!!

Kim Kempf

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Community Member on at

Like (0)

Report

Amended: I'd like the report by Sales Transaction, not by line item.

Was this reply helpful? Yes No
Suggested answer

L Vail 65,271 on at

Like (0)

Report

Create a SmartList Object with the SalesTransactions view and the Sales/Purchases Tax Schedule Master.

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Leslie. I'm not sure what you mean by SmartList Object. I did successfully create a report in SmartList Designer, but it does it by line item. So, I have a separate line for each line item, and that's multiplied by the number of tax detail id on the order. In addition to that, I get a separate line for the total that appears on the Sales Tax Summary Inquiry screen, which is the information I want. I would be happy with just the information that appears on the Sales Tax Summary Inquiry screen and could use a separate report for the other info and merge them in Excel. The Views I am using are: Sales Taxes Work and History, and Sales Transactions.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi Kim,

I'm confused now. What would the report include. There is a tax schedule ID at the header level, and there is a tax schedule ID on each line. Each of these tax schedule ID's have a number of Tax Details assigned to them. Are you looking for your information according to the SOP header, or do you want a record for each SOP Line? If you are only wanting information from the SOP header, the SalesTransactions view will get you there. It sounds like you may be using the SalesLineItems view.

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Leslie,

Thanks for taking the time to try to understand what I'm asking. I would provide screen shots, but I don't see how to do that here.

I am looking for the Tax Detail ID's that will ultimately appear on the sales tax reports once the order is transferred to an invoice and posted. On the order at the bottom where the total tax is listed, I click on the arrow and I can see all the tax details and amounts. That is what I believe ultimately ends up on the Sales Tax Reports. We had some issues with that not being populated, even though the line items had tax schedules/details being populated. It seems to be on ongoing issue ,so I want a report that I can run ever once in a while to check and make sure the tax details are not missing.

As I said, I did come up with a SmartList report I created in SmartList Designer, but instead of just giving me the data I need, I also get the Tax Details for all line items. There is no way for me to differentiate between the two on my report. I would be happy to email you screen shots, if you'd be okay with that.

Was this reply helpful? Yes No
Suggested answer

Victoria Yudin 22,769 on at

Like (0)

Report

Hi Kim,

I believe the detail you want is in the SOP10105 table, but only at the line item level. So you would need to sum it up to get the summary. That's all GP is doing on the window you're looking at - adding up the details from all the items on the transaction. I don't believe it is actually storing those in summary until the transaction is posted. At that time the information gets added to the TX30000 table at the summary level.

The SOP10105 table also shows all the SOP transactions (posted and unposted), so you'd want to filter out the posted.

So for example, if you wanted to create your own SQL for that, you could do something like this:

select

h.SOPNUMBE [SOP Number],

h.SOPTYPE [SOP Type],

t.TAXDTLID [Tax Detail ID],

t.TAXDTSLS [Total Sales],

t.TDTTXSLS [Total Taxable Sales],

sum(t.STAXAMNT) [Tax]

from SOP10100 h

inner join SOP10105 t

on h.SOPTYPE = t.SOPTYPE

and h.SOPNUMBE = t.SOPNUMBE

group by h.SOPNUMBE, h.SOPTYPE,

t.TAXDTLID, t.TAXDTSLS, t.TDTTXSLS

Was this reply helpful? Yes No