Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Daryle Ridley]

I need to report for all invoices for one state.  I am pulling the info into excel.  I need to show how much of the total or subtotal of the invoices is taxable.   I was hoping the Taxable Tax Amount (TXBTXAMT) would be the field I would need, but it appears to be all zeros.  I am looking in the SOP30200 (and the ‘SalesTransactions’ view for easier readability)

I know I can look in the SOP10105 table to see “Original Total Taxable Sales” (ORTXSLS) field, however that table has many lines for each tax schedule and each line item.   Not sure how I would link that correctly.  

Thanks.

——————————
Daryle Ridley
Director of IT
Formetco
Duluth GA
——————————

[Danny Maxwell]

Try SOP30300 (sales transaction amounts history)

select SOPD.SOPNUMBE,SUM(TAXAMNT) from SOP30300 SOPD –sales transaction amounts (history)
where SOPD.SOPTYPE = 3 and SOPD.[STATE] = ‘YOUR STATE’
group by SOPD.SOPNUMBE;

——————————
Danny Maxwell
Business Analyst
AFL
Duncan SC
——————————
——————————————-

[Daryle Ridley]

Thanks for your reply, however your query is adding the Tax amount.   I am able to get the tax amount charged on an invoice.  What I need is how much of the sales amount shown is taxable.   It does show this value in the SOP10105, however it shows it several times depending on the tax details it applies to.  

thanks.

Daryle

——————————
Daryle Ridley
Director of IT
Formetco
Duluth GA
——————————
——————————————-

[Jo deRuiter]

Hi @Daryle Ridley

It seems like you are looking for the tax on the sales tax or taxable tax – please confirm and we may have more specific help for you.

Also, in your system are all items taxable?  Are there any further or complicated rules for taxing for you guys??

——————————
Kindest Regards,
Jo deRuiter , MCP, DCP
“That GP Red Head”
AISLING DYNAMICS CONSULTING, LLC
Advanced Credentialed Professional-Dynamics GP
GPUG Academy Instructor
Co-Chair: GPUG GP Credentialing Exam Council
Chairman, GPUG Partner Advisory Board
Principal Financial Systems Consultant
Milwaukee, WI
770-906-4504 (Cell)
——————————
——————————————-

[Daryle Ridley]

Im looking for the amount of the subtotal that is taxable and the amount that is not taxable.   We do charge labor and several other items that don’t get taxed.   For the most part, they are usually a separate order, but I am betting I will find some with a taxable item and a non taxable item together.

——————————
Daryle Ridley
Director of IT
Formetco
Duluth GA
——————————
——————————————-

[Blair Christensen]

?I wrote this for our Accountants, which lists out the taxable amounts, the state, and factors in returns.

SELECT CASE WHEN RTRIM(i.[STATE]) = ” THEN RTRIM(c.[STATE])

ELSE RTRIM(i.[STATE])

END as ShipTo

, RTRIM(c.[STATE]) as BillTo

, RTRIM(i.CUSTNAME) as CustomerName

, RTRIM(i.SOPNUMBE) as InvoiceNumber

, i.INVODATE as InvoiceDate

, i.SUBTOTAL as Subtotal

, i.MISCAMNT as Warehouse

, i.FRTAMNT as Freight

, RTRIM(i.LOCNCODE) as Supplier

FROM LOGIS..SOP30200 i

LEFT JOIN LOGIS..RM00102 c ON i.CUSTNMBR = c.CUSTNMBR

AND i.PRBTADCD = c.ADRSCODE

WHERE i.VOIDSTTS = 0

AND i.SOPTYPE = 3

AND i.INVODATE >= @startdate

AND i.INVODATE <= @enddate

/* Returns */

UNION ALL

SELECT CASE WHEN RTRIM(i.[STATE]) = ” THEN RTRIM(c.[STATE])

ELSE RTRIM(i.[STATE])

END as ShipTo

, RTRIM(c.[STATE]) as BillTo

, RTRIM(CUSTNAME) as CustomerName

, RTRIM(SOPNUMBE) as InvoiceNumber

, RETUDATE as InvoiceDate

, convert(money,- SUBTOTAL) as Subtotal

, i.MISCAMNT as Warehouse

, i.FRTAMNT as Freight

, RTRIM(i.LOCNCODE) as Supplier

FROM LOGIS..SOP30200 i

LEFT JOIN LOGIS..RM00102 c ON i.CUSTNMBR = c.CUSTNMBR

AND i.PRBTADCD = c.ADRSCODE

WHERE i.VOIDSTTS = 0

AND i.SOPTYPE = 4

AND i.RETUDATE >= @startdate

AND i.RETUDATE <= @enddate

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Daryle Ridley]

Blair, thanks for the query.  I looked at it and it appears to only show the Subtotal that is in the SOP30200 table.  My question is, how much of the Subtotal in the SOP30200 table is taxable?

——————————
Daryle Ridley
Director of IT
Formetco
Duluth GA
——————————
——————————————-

[Kerry Hataley]

To get more details from the SOP30200 and what is taxable within the subtotal, you need to dig into the details table. SOP30300. By linking the fields SOPNUMBE you can find the line items and their tax rate, since they could not only be exempt but also at a different amounts. (Tax Schedules)

If you need more detail, then link into SOP10105 – This will give you a full look at all the tax info.

——————————
Kerry Hataley
CEO & President
Nanook Software, Inc
——————————
——————————————-